|
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
|
|
|
|
|
IF @dataType='image'
i have an error, it's stop like that :
,'ÿØÿà
any idea ?
|
|
|
|
|
|
I'm very new to SQL and this program has been great. I'm currently trying to make the output for int,bit,numeric and decimal come without the quotes, Any suggestions?
Thanks.
Jelias
|
|
|
|
|
Ok,I have found the answer:
SET @stringData=@stringData+''''+'''+isnull(convert(varchar200),'+@colName+'),''NULL'')+'',''+'
Thanks!!
|
|
|
|
|
Almost - varchar200) should be varchar(200) though
Nice script - very useful!
|
|
|
|
|
Often, you will encounter a column name that matches a SQL keyword (e.g., Desc).
The column name must then be enclosed in brackets in sql scripts.
I had to modify this stored proc by putting brackets around the @colName param in the code ( e.g., ...+['+@colName+']+....)
|
|
|
|
|
The Script Work Well. Thank You.
Marcos de Sousa
|
|
|
|
|
Hi,
I downloaded this script & it works fine, but not returns full output, INSERT statement truncated in between, see the below rows generated by this script,
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('10','Puru','V',convert(datetime,'1979-02-23 00:00:00.000',1
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('2','admin',NULL,convert(datetime,NULL,121),convert(datetime
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('3','cfs','cfs',convert(datetime,'1900-01-01 00:00:00.000',1
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('4','Smith','Wilber',convert(datetime,'1900-01-01 00:00:00.0
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('5','Harrison','Ford',convert(datetime,'1900-01-01 00:00:00.
INSERT tabemployee(vcempid,vcempname,vcemplastname,dtdob,dtdoh,vcssno,vcaddress,vccity,vcstate,vccountry,vczipcode,vcphone1,vcphone2,vcroleid,vcusername,vcemptype,vccustid,vcwhid,cdeleted) VALUES('6','John','Sanders',convert(datetime,'1900-01-01 00:00:00.0
what could be the problem,
|
|
|
|
|
Try this - in query analyser, select Tools, Options, select the Results tab then change the "Maximum characters per column:" value to something bigger than 256. Worked for me.
|
|
|
|
|
Hi Sumit,
...great job!
This is a suggestion of how to change your st. proc to handle the "'" within text based datatypes:
a) varchar,char,nchar,nvarchar
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''+''''','''''+'''''+'''''+''''')+'''''+''''',''NULL'')+'',''+'
b) text,ntext:
SET @stringData=@stringData+'''''''''+isnull(REPLACE(cast('+@colName+' as varchar(2000)),'''''+''''','''''+'''''+'''''+'''''),'''')+'''''',''+'
I have tested and it works.
Claudiu
|
|
|
|
|
Thanks Claudiu
I was struggling with this problem for about half an hour before I noticed your excellent solution, much appreciated - I should have read the threads first
|
|
|
|
|
Thanks Claudio!
Sumit Amar
Microsoft Corporation
|
|
|
|
|
Hi !
i also spent a lot of time correcting this behaviour just because i didnt read these replies.
I found this solution and it seems to work (for case a) ):
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE('+@colName+','''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'
are you aware of a St.Proc. that can generate the inserts for all the table in the correct order ?
Some table must be scripted before others (foreign keys, primary key... ) and some records must be inserted before others in tables that have a reference to themselves (this second problem is the one i cant solve easily)
thankx
bye
Wentu
|
|
|
|
|
I am using SQL Server 2000.
|
|
|
|
|
I don't get any output either. Put
USE MyForumName
GO
InsertGenerator utPosts
GO
And got no columns returned. Seemed like a good idea though.
|
|
|
|
|
If there is a timestamp field in the table, the generated sql seems to be incorrect.
|
|
|
|
|