Simple Table Copy

[BrianBurgit]

I have an Access Table that I'd like to have copied to SQL Server 2000 several times a day, since it is updated frequently. The destination of my DTS package uses a Create Table statement, but this results in the data being appended each time I execute the DTS. Do I need to change the SQL statement I have, and if so, to what?

 

[DrSql]

The way approch is,
In your DTS package add step 1 as
truncte mytable
this way you table would be empty prior to your import.


Dr.Sql
Good Luck.

 

[BrianBurgit]

I tried to add the this:

TRUNCATE TABLE myTable but I get an error when I try to save the package, saying myTable does not exist. If I create the Table first, then the CREATE TABLE statement produced an error when I try to save the package, saying myTable already exists.

 

[DrSql]

Either you have to create outside your DTS pacakge, You dont want to try to create everythim DTS runs. You can truncate the table if the schema is same. If you recreate all the time you dont have to use TRUNCATE.

Else need to add this
[tt]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO
[/tt]


Dr.Sql
Good Luck.