DTS for delete and insert in SQL Tables

Question

DTS for delete and insert in SQL Tables

R. M. Joseph

Old Hand

Points: 368
More actions
June 13, 2005 at 7:11 am

#164751

Dear Friends,
I am looking for help to create DTS to complete the following task:
1. Unzip a text file, which contains about 500 rows of SQL statements of delete and insert. This zip file is in a particular folder in the server.
2. After unzip, the text file to be run in the sql server, so that old records be deleted and inserted in the tables.
3. If any Error in the insert, the SQL error message and SQL statement of that particular row in the text file shall be stored in the ErrorsTable.
The text file contains statement like below:
(INSERT INTO [pubs].[dbo].[authors]([au_id], [au_lname], [au_fname])
VALUES(<au_id,id,>, <au_lname,varchar(40),>, <au_fname,varchar(20),&gt
------------------
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I don't have much experience with this, but here's a couple of cents worth.

1. Consider using an Execute Process task or an Execute SQL task containing xp_cmdshell in DTS to run a command line unZIP program. Here are a couple of related threads:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=174174

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=4848

2. xp_cmdshell can execute the unzipped batch command file. master..xp_cmdshell 'c:\Test\Executeupdates.bat'

I'm sorry I don't know offhand how to capture all the results of the statements in a table.

Hope this helps

Greg

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 2

Alternatively, once you've unzipped the file containing SQL statements you can load them into a temp table with a single field using a BULK INSERT command (look in BOL).

Then process each record from the temp table, i.e. each statement, in turn executing them with either EXECUTE with dynamic SQL (i.e. varchar or nvarchar variable containing the statement), or to trap the errors more robustly use sp_executesql (see BOL).

To step thru each statement after loading it into the temp table you can either use a cursor (simple coding, but slower) or copy records into a 2nd table with an identity column and retrieve each statement using the record number in the identity column.