dynamically create insert statements, help !

Question

dynamically create insert statements, help !

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
January 14, 2008 at 10:45 am

#185434

Does anyone have any words of advice/code snippets etc etc on how to dynaically create insert statements e.g with a list of tables names that I feed into information_schema.columns can I generate the header for an insert statement so I can add the "select * from blah" into it using a sql str
many thanks
si

Viewing 7 posts - 1 through 6 (of 6 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Does anyone have any words of advice/code snippets etc etc on how to dynaically create insert statements e.g with a list of tables names that I feed into information_schema.columns can I generate the header for an insert statement so I can add the "select * from blah" into it using a sql str

At my shop we use SQL Scripter because it is free and works really well for creating insert statements.

http://www.sqlscripter.com/

My blog: http://jahaines.blogspot.com

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

thanks but I need to be able to do this dynamically and in code (if possible)

thanks then

~si

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

in 2005

in the object tree

- right click the table

- select script as

- select INSERT to

- select your prefered output

I'm sure theres something similar in 2000

If thats not what you're after then can you repost the problem a bit more descriptively

(apologies if that seems a bit obtuse, I'm in hard of understanding mode today)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Now keeping in mind that dynamic SQL can be very dangerous when you use it around GUI system (SQL injection ,etc....) You could try something like this

Declare @myTable sysname

declare @columnnames as varchar(max)

Select @mytable='testpivot', --my table name - change as appropriate

@columnnames='['

Select @columnnames=@columnnames+sc.name+'],['

from sys.all_columns sc

where sc.object_id=object_ID(@mytable)

ORDER BY column_id

select @columnnames=left(@columnnames,len(@columnnames)-2) --get rid of the open bracket at the end

select @columnnames

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SoCal_DBD Hall of Fame Points: 3381 More actions · Answer 5

You could also use information_schema.columns (something I learned about on this site). Using blatent plagerism of Mr. Matt Miller's response:

Declare @myTable sysname

declare @columnnames as varchar(max)

Select @mytable='YourTableName',

@columnnames='['

Select @columnnames=@columnnames+sc.Column_Name+'],['

from information_schema.columns sc

where Table_Name = @myTable

ORDER BY Ordinal_Position

select @columnnames=left(@columnnames,len(@columnnames)-2) --get rid of the open bracket at the end

select @columnnames

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

I do this sort of thing all the time. I ended up writing a function passing it the table name and which columns to omit (those being identity, computed, time stamp, and values that would be substituted in the select statement).

The function would build the string of columns that could be used in both the insert and select statements. The code you have searching syscolumns should work fine in the function.

This can be very useful in a system that has tables with many columns in each table and you don't have to hand code all of them. I use it mostly for cloning rows where most of the values in the clone record are appropriate for the output record.

Todd Fifield