Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Scripting INSERTs Expand / Collapse
Author
Message
Posted Monday, December 29, 2008 12:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:51 AM
Points: 115, Visits: 312
Comments posted to this topic are about the item Scripting INSERTs
Post #626426
Posted Monday, December 29, 2008 12:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:46 AM
Points: 5,343, Visits: 1,387
Nice article...


Post #626427
Posted Monday, December 29, 2008 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 19, 2014 12:47 AM
Points: 9, Visits: 88
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...
Post #626463
Posted Monday, December 29, 2008 6:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:40 AM
Points: 3, Visits: 49
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
Post #626530
Posted Monday, December 29, 2008 8:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:30 PM
Points: 1, Visits: 126
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.
Post #626612
Posted Monday, December 29, 2008 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 20, 2014 10:51 AM
Points: 36, Visits: 102
google sp_generate_inserts
Post #626614
Posted Monday, December 29, 2008 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 12, 2013 3:13 AM
Points: 2, Visits: 150
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.'
+ CASE WHEN 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.
Post #626618
Posted Monday, December 29, 2008 9:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:51 AM
Points: 115, Visits: 312
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.
Post #626619
Posted Monday, December 29, 2008 10:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
I've been using sp_generate_inserts for a long time, no problems:

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

Post #626653
Posted Monday, December 29, 2008 11:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
Why Insert... Select would not do?


Regards,
Yelena Varshal

Post #626730
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse