'backuping' some rows in ms sql 6.5

[bibyjord]

Hi,

I've a table in a sql 6.5 db that I need to periodically erase.

Before the erase phase I need to save the rows that I'll delete. I've tried to create a script that 'save' this rows' info in a file (in order to import that info if needed).

The script should generate a txt file that contain that info in this format:
insert in 'tablename' ('col1', 'col2', ....'colN') values('values1','values2', ...'valuesN');
insert in 'tablename' ('col1', 'col2', ....'colN') values('values1','values2', ...'valuesN');
........
(one row for every row that I'll delete)
With this file I just can run the sql statement and 'restore' the deleted rows .

I just don't know if this kind of solution is the best solution in this environment;
I've tried to do this by using sql's backup/restore tool but it doesn't work properly (I get duplicated rows or missings rows...)

Do you have any suggestion to give me...my experience is oracle oriented and I'm not so expert with ms sql...

Thanks for you support.
Ciao Roberto

 

[arrowhouse]

Have a look in books online at the BCP (bulk copy) & DTS features of SQL these can be used to create text files that you can then import when/if required.

 

[bibyjord]

I've tried with bcp utility but I get duplicated rows...
with ms sql 6.5 I don't have the query option, so I can export or import the entire table and not a portion of it...

this is my script:

bcp "dbname.owner.tablename" in/out "datafile" -c -E -U"my_user" -P"my_psw"

Thanks for your support
ciao Roberto

 

[tlbroadbent]

Use ISQL command line utility and direct the output to a file. ISQL can execute an ad hoc query selecting from a table or view. It can also execute a stored procedure that creates the statements your want. You can then use ISQL to execute the script that you created. You can find details about ISQL in the SQL Books Online. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.