Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1810
  • Last Modified:

Create INSERT statement with data from existing table

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
quiTech
Asked:
quiTech
  • 7
  • 6
  • 5
  • +3
1 Solution
 
Anthony PerkinsCommented:
With the strongly possibility that I am missing something:

Insert mytable
Select *
from mytable
0
 
quiTechAuthor Commented:
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
 
Anthony PerkinsCommented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
quiTechAuthor Commented:
Found something that works great!  :-)

http://www.nigelrivett.net/sp_CreateDataLoadScript.html
0
 
amit1978Commented:
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
 
chhapia_chintakCommented:
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
 
MikeWalshCommented:
chappia - great proc.. quite impressed with you for making that one.
0
 
Anthony PerkinsCommented:
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
 
MikeWalshCommented:
:) 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
 
MikeWalshCommented:
actually looks like they both got it from the web, amit just changed it a little, chappia didn't even bother.
0
 
Anthony PerkinsCommented:
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
 
MikeWalshCommented:
yeah, what a waste of the copy and paste buffer ;)
0
 
imran_fastCommented:
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
 
MikeWalshCommented:
Imran - Did you read any of the conversation above? The author has found their answer and this question is going to be closed...

0
 
quiTechAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
quiTechAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
quiTechAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
quiTechAuthor Commented:
ah... got it.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 7
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now