|
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
|
|
|
|
|
It looks remarkably similar to this stored procedure:
http://vyaskn.tripod.com/code/generate_inserts.txt
|
|
|
|
|
I don't really care if this is his own work. All I cant tell is that this guy saved my life.
|
|
|
|
|
hi schmiddy!
I looked at your link, I did not understand what is the similarity between that one and my code except for that they're for the same purpose.
|
|
|
|
|