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!!! :-)
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!!! :-)
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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',
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+''
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+''
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+''
isnull(cast(convert(varbin
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,le
VALUES(''+ ' + substring(@stringData,0,le
FROM '+@tableName
exec sp_executesql @query --load and run the built query
CLOSE cursCol
DEALLOCATE cursCol
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',
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+''
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+''
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+''
isnull(cast(convert(varbin
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,le
VALUES(''+ ' + substring(@stringData,0,le
FROM '+@tableName
exec sp_executesql @query --load and run the built query
CLOSE cursCol
DEALLOCATE cursCol
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
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',
BEGIN
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+''
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+''
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+''
--SELECT 'INSERT Authorizations(StatusDate)
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
-- 'convert(datetime,'+isnull
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+''
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+''
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
END
SET @string=@string+@colName+'
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,le
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.
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
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',
BEGIN
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+''
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+''
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+''
--SELECT 'INSERT Authorizations(StatusDate)
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
-- 'convert(datetime,'+isnull
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+''
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+''
--SET @stringData=@stringData+''
SET @stringData=@stringData+''
END
SET @string=@string+@colName+'
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,le
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.
chappia - great proc.. quite impressed with you for making that one.
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
CREATE PROC InsertGenerator
:) I e-mailed netminder about that, anthony.. that and some other posts (the majority of them).... also on a few web sites, every line.
actually looks like they both got it from the web, amit just changed it a little, chappia didn't even bother.
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?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
yeah, what a waste of the copy and paste buffer ;)
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
[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
Imran - Did you read any of the conversation above? The author has found their answer and this question is going to be closed...
ASKER
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?
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?
>>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?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
Yes and again:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
ASKER
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?
>>Do I post a request here?<<
Coorect. Something like:
Please close the question at https://www.experts-exchange.com/questions/21896508/Create-INSERT-statement-with-data-from-existing-table.html as I found a solution independently and posted it.
Coorect. Something like:
Please close the question at https://www.experts-exchange.com/questions/21896508/Create-INSERT-statement-with-data-from-existing-table.html as I found a solution independently and posted it.
ASKER
Please close the question at https://www.experts-exchange.com/questions/21896508/Create-INSERT-statement-with-data-from-existing-table.html as I found a solution independently and posted it.
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?
<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?
ASKER
ah... got it. Thanks!
Insert mytable
Select *
from mytable