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 «««23456»»

Generating Insert Statements Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 12:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
i have written some code to help developers spend less time on scripting basic statements.

the first link is a demonstration of this:
[/url]

the second one is the actual code that uses metadata to easily script select, insert and update statements:

[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/]


the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:

[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]
Post #777284
Posted Wednesday, August 26, 2009 12:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
i have written some code to help developers spend less time on scripting basic statements.

the first link is a demonstration of this:
[/url]

the second one is the actual code that uses metadata to easily script select, insert and update statements:

[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/]


the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:

[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]
Post #777287
Posted Wednesday, August 26, 2009 12:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
i have written some code to help developers spend less time on scripting basic statements.

the first link is a demonstration of this:
[/url]

the second one is the actual code that uses metadata to easily script select, insert and update statements:

[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/]


the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:

[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]
Post #777305
Posted Thursday, January 7, 2010 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:14 PM
Points: 9, Visits: 61
Not only did I appreciate this script, I *USE* it. I made a slight change to shrink the output a tad (shown below). The negative to my approach is that I must manually delete the very last "union all" piece, but I can live with that. Thanks Boss!

set @script = 'select ''select '' + ' + substring(@values, 11, len(@values)) + ' + '') union all '' from ' + @table;


if ( @is_identity = 1 ) begin
print ('set identity_insert ' + @table + ' on');
end

print 'insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) + ')'
exec sp_executesql @script;

if ( @is_identity = 1 ) begin
print ('set identity_insert ' + @table + ' off');
end



Post #843793
Posted Friday, January 8, 2010 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:14 PM
Points: 9, Visits: 61
And I'll likely page the resulting statements so I can keep inserts to batches of 1,000 at a time -- it works faster this way.




Post #844503
Posted Wednesday, February 10, 2010 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:30 PM
Points: 10, Visits: 63
Excellent Article man...will be really helpful!!
Post #863289
Posted Wednesday, May 12, 2010 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:00 AM
Points: 1, Visits: 15
That is very interesting, bud i have a problem with "float" data type
Post #920413
Posted Friday, May 14, 2010 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 29, 2012 4:39 AM
Points: 40, Visits: 31
already done in ssms 2008, but nice try I might find it useful
Post #921803
Posted Friday, May 14, 2010 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,100, Visits: 371
thank you ... i have been using this script from the time it was first published... is there any change from the first query ?
Post #921875
Posted Friday, May 14, 2010 4:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 5:59 AM
Points: 17, Visits: 18
I've often used a useful stored procedure for this type of thing, also works like a charm! Feel free to check it out at http://vyaskn.tripod.com/code.htm direct links to the stored procedure source for 2000 is http://vyaskn.tripod.com/code/generate_inserts.txt , for 2005/8 its http://vyaskn.tripod.com/code/generate_inserts_2005.txt
Post #921901
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse