Scripting INSERTs

Question

Post reply

Scripting INSERTs

Tony Webster

SSChasing Mays

Points: 610
More actions
December 29, 2008 at 12:15 am

#383645

Comments posted to this topic are about the item Scripting INSERTs

Viewing 15 posts - 1 through 15 (of 22 total)

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

Anipaul

SSC-Insane

Points: 24681

December 29, 2008 at 12:52 am

#918267

Nice article...

Adam Tappis SSC Journeyman Points: 98 More actions · Answer 2

Nicely done. However I would look to apply the the filter condition as early as possible, because on a 1 million row table, the procedure will cursor over all 1 million rows even if the condition limits the result to just 3 rows...

Mike Sofen-356112 Valued Member Points: 57 More actions · Answer 3

Suggested revisions: eliminate the cursor with a "while" loop, and use the metadata more effectively to eliminate the function calls. The input param that is nullable simply needs the standard "= null", rather than " = '1=1' ". Use "set nocount on" at the beginning of the sproc to eliminate the bazillion return messages this sproc generates.

And the biggies: you're taking the top 100 columns from a table. That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.

Mike Sofen

San Diego, CA

grant.owens Old Hand Points: 351 More actions · Answer 4

Nice stuff and very useful. There is a more sophisticated version of this at http://vyaskn.tripod.com/code.htm#inserts that i've used fairly extensively.

Etni Valued Member Points: 68 More actions · Answer 5

Etni

Valued Member

Points: 68

December 29, 2008 at 8:56 am

#918415

google sp_generate_inserts

dfarran Grasshopper Points: 16 More actions · Answer 6

Nice article. You can actually drop the cursor completely and replace with the following

DECLARE @fields nvarchar(4000)

DECLARE @values nvarchar(4000)

SET @fields = ''

SET @values = ''

SELECT @fields = @fields+ ',[' + sc.name + ']' ,

@values = @values+ '+'',''+dbo.'

+ CASEWHEN st.name = 'money' THEN 'spMVal'

ELSE 'spVal'

END

+ '([' + sc.name + '])'

FROM syscolumns sc

JOIN sysobjects so ON so.id = sc.id

JOIN systypes st ON st.xusertype = sc.xusertype

WHERE so.name = @table

AND st.name <> 'text'

ORDER BY sc.colorder

PRINT @fields

PRINT @values

In SQL 2000 there is the (mentioned in the caveats) issue with nvarchar(4000) meaning you will potentially run out of string space, this is much less of an issue in 2005 with nvarchar(MAX).

I hope someone finds this SELECT @STR = @STR + ... useful, I know I have. I have found some occasions where it only returns one line, but if you include TOP 100 percent it will generally solve this issue.

Tony Webster SSChasing Mays Points: 610 More actions · Answer 7

That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.

All good points - this was never indented to be complete to that degree. What actually is more likely to happen before you hit 100 columns I think is that the varchar variable used to assemble the SQL would overflow its length.

I don't actually run what it produces without checking it visually first. All I was after was something simple to fulfill a particular need - a plank to get over the stream at the bottom of the garden - a fully stress-tested suspension bridge it certainly isn't! That said, there are some good ideas here and elsewhere in the thread for improving it relatively simply.

Alexander Kuznetsov SSCrazy Points: 2217 More actions · Answer 8

I've been using sp_generate_inserts for a long time, no problems:

http://vyaskn.tripod.com/code/generate_inserts.txt

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 9

Why Insert... Select would not do?

Regards,Yelena Varsha

emiddlebrooks Hall of Fame Points: 3393 More actions · Answer 10

Yelena Varshal (12/29/2008)
Why Insert... Select would not do?

I assume that your question is why you couldn't directly insert into one DB by selecting the data from another?

This is meant to be more portable than that so that you could ship the script from one place to another without being connected. Personally I do this a lot via email to a client so that all they have to do is open the script and run it.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 11

I mostly use CSV / BCP or MS ACCESS for that.

Regards,Yelena Varsha

Mike Sofen-356112 Valued Member Points: 57 More actions · Answer 12

This type of query is used when we need to create a script that mirrors the data in a given table to a sql script. This often used to place a base taxonomy into source control, or to create build scripts for a new server, or rollback scripts for a release. In all of these cases, we need the actual insert statements that will create the data, not the data by itself. Make sense?

luckyHailong SSC Rookie Points: 36 More actions · Answer 13

if the data has "--" , "[" or other keyword does it work?

NewOzzie Valued Member Points: 74 More actions · Answer 14

This is also not catering for binary/varbinary columns correctly