Solved

Create INSERT statement with data from existing table

Posted on 2006-06-22
22
1,797 Views
Last Modified: 2008-01-09
Hello All,

Does anyone have a T-SQL script that creates insert statements from an existing table, using the current table's data?

For example, let's say we have a table called [mytable] and the table contains two columns [a] and [b], with the following data:

[a]          [b]
----------  ----------
a1           b1
a2           b2

I would like a script that would create the following insert statements:

insert into [mytable] ([a], [b]) values ('a1','b1');
insert into [mytable] ([a], [b]) values ('a2','b2');

I have about 10 tables against which I would need to run this, all with varying numbers of rows and columns, so I'm hoping for something generic.

Thanks in advance!!!  :-)
0
Comment
Question by:quiTech
  • 7
  • 6
  • 5
  • +3
22 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16965482
With the strongly possibility that I am missing something:

Insert mytable
Select *
from mytable
0
 

Author Comment

by:quiTech
ID: 16965506
I should have been clearer in my question.  The problem is that the source table will not be available when inserting the data.  Otherwise, yes, your suggestion would work great :-)

Basically, I have a large database, for which I need to make some "create" scripts.  I've done all the create table, stored procedure etc.  But many of the tables have static data that I also need to add to these scripts.

The idea is basically to take these scripts to a number of other servers, and run them to create "copies" of the original database.

Doing a full backup would be the easiest way to do this, but unfortunately, this is not an option.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16965539
Ah, yes,  that makes sense and that is exactly what Red Gate's SQL Data Compare does, but I have never had the need to do it myself.  Hopefully, someone will step up to the plate.
0
 

Accepted Solution

by:
quiTech earned 0 total points
ID: 16965862
Found something that works great!  :-)

http://www.nigelrivett.net/sp_CreateDataLoadScript.html
0
 
LVL 5

Expert Comment

by:amit1978
ID: 16965874
Hope this will solve your prob,


Declare @tableName varchar(100)
--Table name for which we have to generate insert
Set @tableName = '<Table Name>' script

--Declare a cursor to retrieve column specific information
--for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
    WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half
                               --of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data
                                   --(VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned
                                 --for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
    begin
    print 'Table '+@tableName+' not found, processing skipped.'
    close curscol
    deallocate curscol
    return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
    SET @stringData=@stringData+'''''''''+
            isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype
                                 --is text or something else
BEGIN
    SET @stringData=@stringData+'''''''''+
          isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted
                       --from varchar implicitly
BEGIN
    SET @stringData=@stringData+'''convert(money,''''''+
        isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
    SET @stringData=@stringData+'''convert(datetime,''''''+
        isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE
IF @dataType='image'
BEGIN
    SET @stringData=@stringData+'''''''''+
       isnull(cast(convert(varbinary,'+@colName+')
       as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
    SET @stringData=@stringData+'''''''''+
          isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000) -- provide for the whole query,
                              -- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
    VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
    FROM '+@tableName
exec sp_executesql @query --load and run the built query

CLOSE cursCol
DEALLOCATE cursCol
0
 
LVL 3

Expert Comment

by:chhapia_chintak
ID: 16966430
Create procedure.

CREATE PROC InsertGenerator
(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
      begin
      print 'Table '+@tableName+' not found, processing skipped.'
      close curscol
      deallocate curscol
      return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
      --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
      SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
      SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
      SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
      --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
      --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
      --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
      SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
      SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
      --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
      --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
      SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query

CLOSE cursCol
DEALLOCATE cursCol


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


pass table name to procedure.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16975041
chappia - great proc.. quite impressed with you for making that one.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16975410
So am I.  I understand all the rest of the code that you copied and pasted from the previous comment from amit1978, the part that I am especially impressed with is the line:

CREATE PROC InsertGenerator

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16975417
:) I e-mailed netminder about that, anthony.. that and some other posts (the majority of them).... also on a few web sites, every line.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16975419
actually looks like they both got it from the web, amit just changed it a little, chappia didn't even bother.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16975434
The sad and pathetic part about it is that it is all moot:  The questioner has already found an answer on Nigel's website and should request that the question be PAQ'd and points refunded.  In case they do not know the procedure, here it is from the EE Guidelines:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16975438
yeah, what a waste of the copy and paste buffer ;)
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16978372
SELECT replace ('insert into MyTable(
[a],
[b],
[c]
) VALUES (' +
'''' + isnull (replace (cast([a] as varchar(4000)), '''', ''''''), 'null') + '''' + ',' +
'''' + isnull (replace (cast([b] as varchar(4000)), '''', ''''''), 'null') + '''' + ',' +
'''' + isnull (replace (cast([c] as varchar(4000)), '''', ''''''), 'null') + '''' + ')'
, '''null''', 'null') +CHAR(10) + 'GO' FROM MyTable
 
GO
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16978916
Imran - Did you read any of the conversation above? The author has found their answer and this question is going to be closed...

0
 

Author Comment

by:quiTech
ID: 16979337
Thanks to everyone for their replies.  Looks like some good solutions were posted.

From what I'm reading, I suppose the correct course of action would be to have the question closed.  Alternatively, I could split the points between amit and chhapia for posting solutions that both seem to work.  

Suggestions?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16979462
>>From what I'm reading, I suppose the correct course of action would be to have the question closed.<<
Yes and again:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 

Author Comment

by:quiTech
ID: 16979466
That's what I thought... The link suggests to ask the moderator to close the question.  At the risk of sounding dumb, how do I do this?  Do I post a request here?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16979547
>>Do I post a request here?<<
Coorect.  Something like:

Please close the question at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21896508.html as I found a solution independently and posted it.
0
 

Author Comment

by:quiTech
ID: 16979608
Please close the question at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21896508.html as I found a solution independently and posted it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16979828
Let's try that again:

<quote>
Post a question in the Community Support topic area asking for a refund, and asking the Moderators to close the question. You'll be required to post your solution in your original question. A Moderator will post a notice of your request which will give the participants 96 hours to object to the refund. Note that if it resembles one of the suggested comments, the likelihood is that your request will not be granted, but rather, the points will be awarded to the Expert who makes the suggestion. In your Community Support request, remember to post a link to the original question.
</quote>

The key point is that you should "Post a question in the Community Support topic area"

Capice?
0
 

Author Comment

by:quiTech
ID: 16979879
ah... got it.  Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now