Solved

Create INSERT statement with data from existing table

Posted on 2006-06-22
22
1,802 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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