|
Almost exactly what I was looking for and saved me a heap of time.
I added a second parameter to pass an optional where clause through to constrain the output for my purposes as well, but was easy to do.
Cheers, Adrian
|
|
|
|
|
Tks a Lot for this useful script!
|
|
|
|
|
|
I had a unique situation where I had to migrate a SQL DB to a server where I did not have any access but a web SQL prompt.
The only way to migrate data was to execute the SQL insert script on the web SQL prompt.
Your script helped a lot.
-Uday
udyaa
|
|
|
|
|
Thanks very much for this slick and very efficient piece of code. It saved me hours of work and I really appreciate your talent and creativity. Great code!
Again, Thanks!!!
|
|
|
|
|
How can I get this to emulate SQLPUBWIZ?
I have five tables that I would like to script a INSERT query for.
The resulting file would contain the INSERT code for all five tables.
Perhaps a batch file that comes loaded with the 5 file names?
Which in turn generates a 2nd .sql file.
Which would then be executed on the remote Host.
My attempts to do this were frustrated by the PRINT command getting confused with special characters ( ',", etc), When I was attempting to have TSQL code written to the resulting script file.
Thanks
modified on Tuesday, May 12, 2009 7:23 AM
|
|
|
|
|
with the little changes of table_schema for the table, so i can use this script now.
------------------------------------------------------------------------------------
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,table_schema 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
DECLARE @schemaType nvarchar(100) --table schema value for correct schema execution
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
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='INSERT '+@schemaType+'.'+@tableName+'('
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@schemaType+'.'+@tableName
Print @query
exec sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO
|
|
|
|
|
thank you for this update!
|
|
|
|
|
Just what I was looking for to make upload data in GoDaddy easier, since they do not allow you to connect to SQL Server from a local enterprise manager.
Great work!!!
|
|
|
|
|
Oye gracias muy bueno tu aporte!! Me salvaste de un buen trabajo
Galo.
|
|
|
|
|
create PROCEDURE [dbo].[procUtils_InsertGenerator]
(
@domain_user varchar(50),
@tableName varchar(100)
)
as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
--old takes the PK's SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
/* NEW without PK's */
select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName ) and c.is_identity=0
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
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @tableName , @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 @tableName , @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
/*
use MyDataBase
go
DECLARE @RC int
DECLARE @domain_user varchar(50)
DECLARE @tableName varchar(100)
-- TODO: Set parameter values here.
set @domain_user='myDomainUser'
set @tableName = 'MyTableName'
EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]
@domain_user
,@tableName
*/
<div class="ForumMod">modified on Wednesday, November 19, 2008 1:41 PM</div>
|
|
|
|
|
this has been very useful to me.
|
|
|
|
|
<code>
-- MSSQL 2005
declare @table_name nvarchar(100)
declare @columns nvarchar(max)
declare @values nvarchar(max)
declare @identity bit
declare @sql nvarchar(max)
set @table_name = 'MyTable'
set @columns =''
set @values =''
set @identity = 0
select
@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns = @columns + ',' + '['+column_name+']',
@values = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] as varbinary(max))),''NULL'')'
from
information_schema.columns
where
table_name = @table_name and data_type != 'timestamp'
set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'
</code>
|
|
|
|
|
You are right, this is much better!
|
|
|
|
|
Where have you been
This is much cool
Any thing am ready
|
|
|
|
|
I ran the SP and it did produced an inserts that breaks if the fields of type varchar include the (') character. Here is an updated version
Hany~
-- ---
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER 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('''''+'''''+REPLACE('+ @colName +',char(39), (char(39) + char(39)+ char(39))) +'''''+''''',''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
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
|
|
When I came across your original code snippet, I was smiling from ear to ear! But then I realized the problem with single quotes in my varchar and ntext fields. So then I found the fix for the single quote problem and Altered the Proc. So now I'm getting this error....
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' FROM gen_zIDXProperties'.
Any ideas? I copied the code correctly, I checked it several times.
Thanks,
Chris
|
|
|
|
|
This SP does not take into account Auto-Increment PK Columns, so the generated sql statements have to be modified to work.
Still a usefull SP though!
|
|
|
|
|
Saved me a lot of time...
|
|
|
|
|
Thanks for the script. It was very helpful to me. Saved a lot of time. Works like a marvel.
I am trying to generate INSERT script where each statement becomes more than 8192 character. It does not show on query window, nor does it get writen properly in output file properly. Statements get truncated because there are too many texts in the table.
Do you have any solution?
Regards,
Omar AL Zabir
Visual C# MVP
|
|
|
|
|
Try SQL Scripter to generate data scripts.
All commands supported: Insert, Update, Delete + Combinations...
It's free.
http://www.sqlscripter.com
|
|
|
|
|
Thanks for the link. Even the stored proc is also good.
|
|
|
|
|
This nearly did what I needed it to do, except for escaping the quotes within the data. Perhaps you could update the stored procedure to fix this..
|
|
|
|
|
I had some trouble generating the insert statements on converting to varchar because some of the collumns in my database used a different COLLATE.
I therefor enhanced the statements with the COLLATE clauses needed:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER 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
print @colname + ' ' + @datatype
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''+''''','''''+'''''+'''''+''''')+'''''+''''',''NULL'')+'',''+'
--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(REPLACE(cast('+@colName+' as varchar(2000))COLLATE database_default
,'''''+''''','''''+'''''+'''''+'''''),'''')+'''''',''+'
--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)) COLLATE database_default
,''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='timestamp'
BEGIN
SET @stringData=@stringData+'''''+ ''NULL'' +'',''+'
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) COLLATE database_default
,['+@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)) COLLATE database_default,''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--print @colname + 'other datatype'
SET @stringData=@stringData+''''+ '''+isnull(convert(varchar(200),['+ @colName + ']) COLLATE database_default,''NULL'')+'',''+'
--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
print 'All fields fetched'
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
print @query
exec sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Milo van der Linden
GIS consultant
AVD-ict
|
|
|
|
|
Your code does not accept null values to be inserted in datetime fields instead it generates
01/01/1900. How can I allow the code to insert nulls using the code? Thanks
Yomi
|
|
|
|
|