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 Friday, May 14, 2010 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:48 PM
Points: 385, Visits: 387
You have a really good script for this in this url:

http://vyaskn.tripod.com/code/generate_inserts.txt
Written by: Narayana Vyas Kondreddi

I used a lot, its really nice!!! .

Post #921956
Posted Friday, May 14, 2010 8:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
Emito (5/14/2010)
You have a really good script for this in this url:

http://vyaskn.tripod.com/code/generate_inserts.txt
Written by: Narayana Vyas Kondreddi

I used a lot, its really nice!!! .


Yes, the old script you mention has been used by many in the past. It does not handle some data types, such as image and varbinary(max). Additionally, that script has a while loop in it used just to get column names and their data types. Using the loop is not a good idea when you can use a rather simple select statement to do the work. I specifically mentioned in the beginning of the article that I am offering no cursors, no while loops, no bunch of single quotes solution to the task of generating insert statements.

My script also suffers from data type limitations, but this is only because of the small bugs in it. For example, I simply did not check floats and handling them requires small tweaks to the script. However, my script can potentially handle the data types which are not handled by the script to which you refer.

Oleg
Post #922101
Posted Friday, May 14, 2010 10:54 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: Today @ 12:00 AM
Points: 3,250, Visits: 522
Hi,

Well, in SSMS 2008 R2 this feature (scripting data) is no longer available, so this solutions is usefull again .

Another option is to use this add-in to SSMS (it can scripting out table content): http://www.ssmstoolspack.com/.

Anyway, this solution is brilliant.

Regards



Slawomir Swietoniowski, MCITP:DBA+Developer (2005/2008)
Post #922190
Posted Friday, May 14, 2010 11:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
Nice article. I'm glad to have finally seen it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #922231
Posted Friday, May 14, 2010 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 2:36 AM
Points: 9, Visits: 30
Oleg

The script is excellent no doubt about it.
The problem with it that it is database specific + you have to connect to the database first.

I had several situations when I was in the airport and all I had an excel file from the customer.

Likely there is another solution which can help you in time of need

http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html

Keep coding,
Mike
Post #922235
Posted Monday, May 17, 2010 3:53 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, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
Nice Article. Very helpful.

Thanks.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #922779
Posted Monday, May 17, 2010 4:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 33, Visits: 288
Nice script !!!
I suggest a small improving,
special handling not only of text datatype
but also of ntext

when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'

You can update your script with it
Post #922783
Posted Tuesday, January 18, 2011 10:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 940, Visits: 1,738
Slawomir Swietoniowski (5/14/2010)
Hi,

Well, in SSMS 2008 R2 this feature (scripting data) is no longer available, so this solutions is usefull again .

Another option is to use this add-in to SSMS (it can scripting out table content): http://www.ssmstoolspack.com/.

Anyway, this solution is brilliant.

Regards



Slawomir,

The scripting data feature is still available in SSMS 2008 R2, but it appears to be under a different heading from SSMS 2008. The property option is now available under the "General" header and called "Type of data to script". It has 3 options in the drop down: Data Only, Schema and data, Schema Only. For those not aware, you can get to the scripting: by right clicking the mouse on a database, Tasks > Generate Scripts... > Choose "Select specific database objects" > Select the tables you want to script > then select the "Advanced" button to get to the "Type of data to script" property.

Thanks,
John D
Post #1049506
Posted Monday, March 17, 2014 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:04 PM
Points: 44, Visits: 124

should become
case data_type 
when 'float' then 'cast([' + column_name + '] as decimal(38, 10))'
when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'



Oleg, in case of type text what is the difference from the first version ?

I have a text column with 300000 special characteres of length and doesn't worked with your procedure.

Through the option generate scripts from SSMS 2012 it's ok, but usually i always use your script however in this table doesn't worked with text column because a lot of special characters. I think the problem is the lost of data when cast to hexa .. i don't know .

Thanks a lot for your proc.

Alexandre Araujo

Post #1551896
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse