<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jerrin
»
create an insert statement
create an insert statement
Rate Topic
Display Mode
Topic Options
Author
Message
Jerrin
Jerrin
Posted Sunday, September 23, 2007 6:24 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 02, 2010 6:52 AM
Points: 158,
Visits: 52
Comments posted to this topic are about the item
create an insert statement
Post #401777
Michael Wasson
Michael Wasson
Posted Monday, October 01, 2007 6:45 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, October 22, 2007 7:30 AM
Points: 11,
Visits: 4
It doesn't generate a complete insert statement, just the first part. "Insert into tablename(columns...)"
Also, this functionality is already built into SQL Query Analyzer.
Post #404974
Michael Wasson
Michael Wasson
Posted Monday, October 01, 2007 6:46 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, October 22, 2007 7:30 AM
Points: 11,
Visits: 4
It doesn't generate a complete insert statement, just the first part. 'Insert into tablename(columns...)'
Also, this functionality is already built into SQL Query Analyzer.
Post #404975
Michael Wasson
Michael Wasson
Posted Monday, October 01, 2007 6:46 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, October 22, 2007 7:30 AM
Points: 11,
Visits: 4
It doesn't generate a complete insert statement, just the first part.
Also, this functionality is already built into SQL Query Analyzer.
Post #404976
Michael Wasson
Michael Wasson
Posted Monday, October 01, 2007 6:47 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, October 22, 2007 7:30 AM
Points: 11,
Visits: 4
It doesn't generate a complete insert statement, just the first part.
Also, this functionality is already built into SQL Query Analyzer.
Post #404977
nigel.
nigel.
Posted Thursday, October 04, 2007 7:58 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164,
Visits: 2,603
Doesn't need to use a cursor, it can be replaced by:
SELECT @InsertQuery = @InsertQuery + column_name + ',' from ##Temp_Generate
Hope Jeff M doesn't see this ;)
--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden
Post #406796
Prasad Bhogadi
Prasad Bhogadi
Posted Friday, October 12, 2007 4:07 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:00 AM
Points: 967,
Visits: 447
Yes, its a good suggestion you don't really need to have a cursor to build the insert script. Also if it can be modified to build the script for the complete table if ordinal position is not specified, it would be more handy.
Prasad Bhogadi
Post #409897
Jeff Moden
Jeff Moden
Posted Friday, October 12, 2007 8:32 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
Nigel Rutter (10/4/2007)
Doesn't need to use a cursor, it can be replaced by:
SELECT @InsertQuery = @InsertQuery + column_name + ',' from ##Temp_Generate
Hope Jeff M doesn't see this ;)
Heh... I got'cha covered, Nigel. :D And almost perfect... my only question would be, what are you going to do with the trailing comma ;)
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #410247
Jeff Moden
Jeff Moden
Posted Friday, October 12, 2007 9:14 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
Thought I'd post something folks can play with... could easily be turned into a proc... can be easily modified for an Insert/Values instead of an Insert/Select. With a little forethought, someone could change it to build "CRUD" for a table. All could be modified to ignore auto-fill columns like TimeStamp data-types and IDENTITY columns.
As a side bar, I'm wondering why someone would take the time to build a code generator like the OP and miss the golden opportunity to format the code that's being output instead of genning the same old garbage that the Microsoft template puts out.
... and no bloody Global Temp table or cursor...
--===== Change to an existing database to test this code in
USE NorthWind
GO
--===== Build an Insert/Select for the given table
DECLARE @TableName SYSNAME
SET @TableName = 'dbo.Employees' --<<<< PUT TABLE NAME HERE
DECLARE @Insert VARCHAR(8000)
DECLARE @Select VARCHAR(8000)
DECLARE @MaxNameLength INT
--===== Presets
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT @MaxNameLength = MAX(LEN(sc.Name))
FROM dbo.SYSCOLUMNS sc,
dbo.SYSOBJECTS so
WHERE sc.ID = so.ID
AND so.ID = OBJECT_ID(@TableName)
--===== Makes the INSERT portion of the code
SELECT @Insert = ISNULL(@Insert+','+CHAR(10),'') + SPACE(8)+sc.Name
FROM dbo.SYSCOLUMNS sc,
dbo.SYSOBJECTS so
WHERE sc.ID = so.ID
AND so.ID = OBJECT_ID(@TableName)
ORDER BY sc.ColID
--===== Makes the SELECT portion of the code
SELECT @Select = ISNULL(@Select+','+CHAR(10),'')
+ SPACE(8) + sc.Name + SPACE(@MaxNameLength-LEN(sc.Name))
+ ' = xxxx.' + sc.Name
FROM dbo.SYSCOLUMNS sc,
dbo.SYSOBJECTS so
WHERE sc.ID = so.ID
AND so.ID = OBJECT_ID(@TableName)
ORDER BY sc.ColID
--===== Display the INSERT/SELECT (Copy from Text Results Window)
SELECT ' INSERT INTO '+@TableName+CHAR(10)+SPACE(8)+'('+CHAR(10)+@Insert+CHAR(10)+SPACE(8)+')'
UNION ALL
SELECT ' SELECT' + CHAR(10) + @Select
UNION ALL
SELECT ' FROM sometablename xxxx'
UNION ALL
SELECT ' WHERE someconditions'
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #410249
nigel.
nigel.
Posted Tuesday, October 16, 2007 2:06 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164,
Visits: 2,603
I think in the original script the trailing comma was trimmed off after.
--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden
Post #411150
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
