Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2003-10-08 : 11:15:50
|
| I have some data that is provided to me in an Access D/B. I need to copy the contents of one of the tables to my SQL server. This sounds to me like a job for DTS - BUT Firstly I need to removes all the records in the existing table (but not drop the table) e.g. DROP OCSTable. Will DTS do this and if so how do I set that? I also need to 'TRIM' one of the fields of surplus white space. I'm not sure how to do this with DTS. I know I can specify a query bit but if I need select [Field1] + [Field2] as NewField1 will that work?If not am I better DTS'ing to a different table then copying from there (possibly using an INSERT)or am I better doing it all (albeit slowly) in MS AccessSteve no function beer well without |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-08 : 11:27:59
|
| If you want to use a dts package then include a sql step to truncate/delete the table as a first step then go to the import on success.To trim the can do it in the import but probably better to do it in the table afterwards.I would probably do this in an SP - truncate/delete is then just a statement and import would be openrowset using the jet data provider.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-08 : 12:40:58
|
| DTS can do everything that you have mentioned. Just open up the DTS designer and have a look at the Execute SQL task. This is what you will want to use for the delete/truncate. Then setup your transformation. Use a query as you mentioned, yes you can do select [field1] + ... Then connect the execute sql task with the transformation using an on success workflow.Tara |
|
|
|
|
|
|
Update