Generating Insert Statements

Question

Post reply

Generating Insert Statements

Viewing 14 posts - 46 through 58 (of 58 total)

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

radityan Grasshopper Points: 14 More actions · Answer 1

radityan

Grasshopper

Points: 14

February 10, 2010 at 8:18 am

#1116861

Excellent Article man...will be really helpful!!

sergio.eduardo.linares SSC Enthusiast Points: 181 More actions · Answer 2

That is very interesting, bud i have a problem with "float" data type :unsure:

misus28 Valued Member Points: 68 More actions · Answer 3

already done in ssms 2008, but nice try I might find it useful

ziangij SSCrazy Eights Points: 8044 More actions · Answer 4

thank you :-)... i have been using this script from the time it was first published... is there any change from the first query ?

luke.baughan SSC Rookie Points: 35 More actions · Answer 5

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

Emito Mr or Mrs. 500 Points: 556 More actions · Answer 6

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!!! :-D.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 7

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!!! :-D.

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

Slawomir Swietoniowski SSCarpal Tunnel Points: 4002 More actions · Answer 8

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/[/url].

Anyway, this solution is brilliant.

Regards

Slawomir Swietoniowski, MCITP:DBA+Developer (2005/2008)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Nice article. I'm glad to have finally seen it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mike-1076638 Old Hand Points: 381 More actions · Answer 10

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

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 11

Nice Article. Very helpful.

Thanks.

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

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Federico Iori SSCommitted Points: 1625 More actions · Answer 12

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

John Dempsey Hall of Fame Points: 3225 More actions · Answer 13

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/[/url].
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

Alexandre Araujo SSC Veteran Points: 264 More actions · Answer 14

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