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


Generating Insert Statements


Generating Insert Statements

Author
Message
Emito
Emito
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 575
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
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1817
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
Slawomir Swietoniowski
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3793 Visits: 595
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)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66869 Visits: 18570
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

Mike-1076638
Mike-1076638
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5092 Visits: 5204
Nice Article. Very helpful.

Thanks.

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

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Federico Iori
Federico Iori
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 300
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
John Dempsey
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1409 Visits: 1769
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
Alexandre Araujo
Alexandre Araujo
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 299

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
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