|
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.
|
|
|
|
|
I've added this:
ELSE
IF @dataType='timestamp'
BEGIN
SET @stringData=@stringData+'''''+ ''NULL'' +'',''+'
END
|
|
|
|
|
Thanks!
I owe you a coffee.
|
|
|
|
|
Attached source code has been updated.
|
|
|
|
|
Thank you so much , I need this )
Ibrahim Sobh
|
|
|
|
|