Any way that you can script off data from a table into insert tsql?

[ikirkland]

Hi, was wondering if you can write using t-sql or there are any tools to help get data out of a table into a file of insert statements. Any help would be greatly appreciated.

Regards,

Ian....

 

[skuhlman]

Do you mean that you have select statements and such in a table and you want to execute those statements?
or???
that you want to use data in fields of a table to create new statements and execute them?

 

[tlbroadbent]

I don't believe any of the APIs allow writing to a text file using SQL Insert statements. At least, I've not done so and am not aware of any. However, exporting data from SQL tables to flat files can be easily done using utility tools provided with SQL Server. Read details about the following in SQL BOL.

BCP - Bulk copy command line utility
DTS - Data Transformation Services
OSQL - Command line utility for executing SL statements. The output can be directed to a flat file.
Query Analyzer - The results returned in the query window can be saved to a file.

You can also use VB, Access and myriad other tools to accomplish this. However, I consider DTS to be the best option in SQL 7 or higher. BCP or OSQL would be my next choice. 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.

 

[quisar]

You'll find code for creating insert,update,delete scripts from table at

http://sql.reproms.si.

Check this out. Hope it helps

Quisar

 

[tlbroadbent]

Looks like your question generated three different responses. Each of us seems to interpret the question differently. Perhaps you can enlighten us with more detail about what you need to accomplish. 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.

 

[ikirkland]

no, problem....

Very simple example:

ClassificationID ClassificationCode ClassificationName
------------------------------------------------------------
1 A_Code A_Name

What I was looking to do was somehow create a script that would take the values in a table i.e. The Classification Table above and create an insert script automatically something like this:

insert into Classification(ClassificationID, CassificationCode, ClassificationName) values (1, 'A_Code', 'A_Name')

Wondering if there are any tools to do this???? Or if you can use the sys tables to do something like this....

Cheers,

-Ian

 

[tlbroadbent]

Looks like Quisar provided a good link. I have created a stored procedure that generates an insert statement. If you are interested in the code, let me know. I'm not at work right now so can't post it, yet. 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.

 

[ikirkland]

seems to be in another language, any idea of the exact link???? - cant speak it unfortunately

 

[skuhlman]

Here's one way to do it (you'll need to adjust table and field names):

declare class_cursor cursor for
select * from MyClassifications
OPEN class_cursor

declare @Field1 integer
declare @Field2 nvarchar(50)
declare @Field3 nvarchar(50)
declare @FinalSQL nvarchar(4000)

create table #TempCommands (MyCommand nvarchar(4000))

FETCH NEXT FROM class_cursor
INTO @Field1, @Field2, @Field3

WHILE @@FETCH_STATUS = 0
BEGIN
set @FinalSQL = 'Insert into MyClass (Field1, Field2, Field3)'
set @FinalSQL = @FinalSQL+ ' values ('''+rtrim(convert(char,@Field1))+''','''
set @FinalSQL = @FinalSQL+@Field2+''','''
set @FinalSQL = @FinalSQL+@Field3+''')'
insert into #TempCommands (MyCommand) values (@FinalSQL)
print @FinalSQL
/* EXEC (@FinalSQL) - This command can be used to execute the statement */
FETCH NEXT FROM class_cursor
INTO @Field1, @Field2, @Field3
END
CLOSE class_cursor
DEALLOCATE class_cursor
select * from #TempCommands
drop table #TempCommands

 

[tlbroadbent]

"seems to be in another language"

Look on the bottom left for a language choice list.

You can directly to the scripts page with this link...

http://sql.reproms.si/live/novice/default.asp?sid=1001002004300#41889

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.