SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating Insert Statements


Generating Insert Statements

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2863 Visits: 1883
Comments posted to this topic are about the item Generating Insert Statements
Ola L Martins-329921
Ola L Martins-329921
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 188
Excellent article!
Actually exactly what I needed!
Coming from SQL Server over MySQL and back to SQL Server - suddenly - it annoyed me as @#%& that moving from development to production there was no way of moving the data. (Yes: backup and restore, but in a live environment you don't really want to do that, do you?)

I will tweak it a bit - also generating the actual table prior inserting.
Hopefully I will get it done and I'll post it here - if no one beats me to it...
Chris Howarth-536003
Chris Howarth-536003
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 1173
Nice idea.

An alternative is to use SSMS 2008's in-built data scripting functionality:

http://www.sqlskills.com/BLOGS/PAUL/post/Scripting-schema-AND-data-with-SSMS-in-SQL-2008.aspx

Chris
sample1
sample1
SSC Eights!
SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)

Group: General Forum Members
Points: 987 Visits: 266
It is an amazing idea.
Jeff Paulson
Jeff Paulson
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 141
Looking forward to adapting your code to our environment. Excellent article -- well written. You've raised the bar for other authors.

One small nit -- it's couldn't care less.

Hope to see you post more.
Florian Reischl
Florian Reischl
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17463 Visits: 3934
Nice article :-) and really well written.

Same idea as mine:
http://www.sqlservercentral.com/scripts/Script+Data/65998/


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
chudman
chudman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 437
Brilliant!!! Outside-the-box thinking that turns conventional processes on their head.

Thank you, Oleg.

Jeff Bennett
SQL DBA
Saint Louis, MO



WayneS
WayneS
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42047 Visits: 10790
Chris Howarth (7/1/2009)
Nice idea.

An alternative is to use SSMS 2008's in-built data scripting functionality:

http://www.sqlskills.com/BLOGS/PAUL/post/Scripting-schema-AND-data-with-SSMS-in-SQL-2008.aspx

Chris

I just love it when I start the day off learning something new. With this link, and with the article showing how to do the inserts as binary data, I've learned two things.

Great article, and good link here ... I didn't know that this was in SSMS2008.
Edit: quoted wrong message when posting.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Chris Howarth-536003
Chris Howarth-536003
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 1173
Another new feature of SQL Server 2008 is an extension to the CONVERT function whereby a binary value can be converted to a string representation.

e.g.

SELECT CONVERT(VARCHAR(MAX), 0x484920544845524521, 1)

...returns:

0x484920544845524521

This removes the need for a 'BinToHexStr' function.

Chris
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88403 Visits: 14997
Nice article and very helpful for 2000 and 2005. As mentioned the capability to script data is now part of SSMS 2008.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search