generate insert statements with data

Question

Post reply

generate insert statements with data

sql_learner29

SSCrazy Eights

Points: 9107
More actions
August 6, 2010 at 2:02 pm

#238633

Hi all,
Can someone provide me a function to generate insert satement from a table with data?
thanks
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 15 (of 15 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

If you want to grab a SQL insert statment you would do something like the following.

Let say you have table A and there are records in in like col1 and col2

Then lets say you want to generate a statement to put the data in col3 and col4 in Table B

select 'Insert into TableB (col3, col4) values('''+col1+''''''+col2''')'

from TableA

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Dan.Humphries (8/6/2010)
If you want to grab a SQL insert statment you would do something like the following.
Let say you have table A and there are records in in like col1 and col2
Then lets say you want to generate a statement to put the data in col3 and col4 in Table B
select 'Insert into TableB (col3, col4) values('''+col1+''''''+col2''')'
from TableA

I actually have such a thing in the "Forum Etiquette" article with an explanation of how to use and what to do for certain things like the MONEY datatype. Take a look at the first link in my signature line below. It's similar to what Dan did above but with a different slant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76996 More actions · Answer 3

I've actually started using the FOR XML clause to create the data and then using sp_xml_preparedocument and OPENXML() to read the data. Here are some of the reasons that I like using XML:

* It will automatically handle NULL expressions. (If your expression is complex you may still need to handle NULLS within your expression.)

* It will automatically convert all datatypes to XML, so you don't have the problems with datatypes like money that Jeff mentioned.

* You specify the datatypes to use when shredding the document.

* You can use an existing table when shredding the document (although doing so will leave out the primary key of the table, even if it's included in your document).

Here's an example using the Information_Schema.Tables view. WARNING: XML is case sensitive

DECLARE @xml xml, @i int

SET @xml = (

SELECT *

FROM Information_Schema.Tables

FOR XML PATH('Tables'), ROOT('root')

)

EXEC sp_xml_preparedocument @i OUTPUT, @xml

SELECT *

FROM OPENXML(@i, '/root/Tables', 2)

WITH (

TABLE_CATALOG varchar(255)

, TABLE_SCHEMA varchar(255)

, TABLE_NAME varchar(255)

, TABLE_TYPE varchar(255)

)

EXEC sp_xml_removedocument @i

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

drew.allen (8/7/2010)
Here's an example using the Information_Schema.Tables view. WARNING: XML is case sensitive

Thanks, Drew. Rumor has it that it's also pretty touchy about special characters like ampersands and the like. Is there a hook you can add to keep those from being a problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijay.s SSCrazy Points: 2161 More actions · Answer 5

A sp is made for that purpose. You can download it from

http://www.vyaskn.tripod.com/code.htm

Regards

Vijay

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 6

I hope this may help you..

*******************************************************************

Set Nocount On

Declare @InTableName Varchar(200)

Set @InTableName = 'Ac_LedgerMaster'

Declare @AllTables Table(Tid Int Identity(1,1),TableName Varchar(200), ObjectId Varchar(50))

Insert Into @AllTables(TableName, ObjectId)

Select NAME, OBJECT_ID From Sys.Objects Where Type = 'U'

And name = Isnull(@InTableName,name)

IF Exists(select 'x' from Sys.objects where type='U' and name = 'TableCol')

DROP TABLE TableCol

Create Table TableCol (ColId Int Identity(1,1),ColName Varchar(100))

Declare @TableMin Int, @TableMax Int

Select @TableMin = 1,

@TableMax = Count(ObjectId)

From @AllTables

Declare @TableName Varchar(200), @ObjectId Varchar(50), @InsertScript Varchar(Max), @ColNameStr Varchar(Max), @SelectStr Varchar(Max)

Declare @ColMin Int, @ColMax Int, @ColName Varchar(100)

While @TableMin <= @TableMax

Begin

Set @InsertScript = 'Insert Into '

Set @SelectStr = ''''+'Select '+''''+'+'

Select @TableName = Ltrim(Rtrim(TableName))

,@ObjectId = Ltrim(Rtrim(ObjectId)) From @AllTables Where Tid = @TableMin

Select @InsertScript = @InsertScript + @TableName +' ( '

--Column details *** BEGINS ***

Truncate Table TableCol

Insert Into TableCol(ColName)

Select name From Sys.Columns Where Object_Id = @ObjectId

Select @ColMin = 1 ,@ColMax = Count(ColId) From TableCol

While @ColMin <= @ColMax

Begin

Select @ColName= Ltrim(Rtrim(Isnull(ColName,''))) From TableCol Where ColId = @ColMin

Select @ColNameStr= Isnull(@ColNameStr,'') + @ColName + Case When @ColMin = @ColMax Then '' else ',' end

Select @SelectStr= @SelectStr + ''''''''''+ '+ Convert(Varchar(200), Isnull('+@ColName+' ,'+''''+''''+') )+' + ''''''''''

+ Case When @ColMin = @ColMax Then '' else '+'+''','''+'+' end

Set @ColMin = @ColMin + 1

End

--Column details *** ENDS ***

Select @InsertScript = @InsertScript + @ColNameStr + ' ) ' --+ ' Values ('

Select @InsertScript

--select @SelectStr

Select @SelectStr = @SelectStr + ' From '+ @TableName + ' With (Nolock)'

Select @SelectStr = 'Select ' + @SelectStr

select @SelectStr

Exec (@SelectStr)

Set @TableMin = @TableMin + 1

Select @ColNameStr = ''

End

--drop table TableCol

Set Nocount Off

*******************************************************************

[font="Comic Sans MS"]Praveen Goud[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

vijay.s (8/9/2010)
A sp is made for that purpose. You can download it from
http://www.vyaskn.tripod.com/code.htm
Regards
Vijay

The link doesn't work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 8

i actually keep the correct link to that script to generate insert statements in my signature on the hopes it gets someone to post their data in the forum:

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76996 More actions · Answer 9

Jeff Moden (8/8/2010)
drew.allen (8/7/2010)
Here's an example using the Information_Schema.Tables view. WARNING: XML is case sensitive
Thanks, Drew. Rumor has it that it's also pretty touchy about special characters like ampersands and the like. Is there a hook you can add to keep those from being a problem?

It will automatically escape certain characters like ampersands, but it will convert them back when you shred the document, so I don't really see that as a problem. If you're building a complex XML document using subqueries that also have FOR XML clauses, you do need to make sure that they are treated as XML, but that is easy to do using the TYPE directive in the subquery's FOR XML clause.

Strings also have problem characters like the single quote ('). While the link you provided correctly handled that problem character, none of the rest of the code based on strings in this thread did. My data is much more likely to contain single quotes than ampersands, so I think that's much more of a problem.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 10

@dan-2,

Thanks

I tried you code, it works fine for varchar datatype but not for datetime columns....

Is there any thing diffrent when I use this for datetime dattype...

select 'Insert into abc

( my1,my2, my3) values('''+my1+''','''+my2+''','''+my3+''')'

from abc

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 11

@jeff,

I used your code with union all and QUOTENAME,

It works fine with all datatypes...

SELECT 'SELECT ' + QUOTENAME(cal,'''')+',' + QUOTENAME(mycol,'''')+',' + CAST( myyear AS VARCHAR)+',' + QUOTENAME(mymonth,'''')+',' + QUOTENAME(seq,'''') + ' UNION ALL' FROM

dbo.abcd

Thanks a lot

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

ssis learner__ (8/9/2010)
@JEFF,
I used your code with union all and QUOTENAME,
It works fine with all datatypes...
SELECT 'SELECT ' + QUOTENAME(cal,'''')+',' + QUOTENAME(mycol,'''')+',' + CAST( myyear AS VARCHAR)+',' + QUOTENAME(mymonth,'''')+',' + QUOTENAME(seq,'''') + ' UNION ALL' FROM
dbo.abcd
Thanks a lot

Thanks for the feedback... glad we could help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 13

Jeff Moden (8/8/2010)
drew.allen (8/7/2010)
Here's an example using the Information_Schema.Tables view. WARNING: XML is case sensitive
Thanks, Drew. Rumor has it that it's also pretty touchy about special characters like ampersands and the like. Is there a hook you can add to keep those from being a problem?

Use

.value('.','VARCHAR(MAX)')

, like this

Edit: Hey! Does this mean I've (finally) taught Jeff something? :w00t:

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 14

You can generate INSERT script using VARBINARY datatype. Read the article by Oleg Netchaev:

http://www.sqlservercentral.com/articles/T-SQL/66987/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]