Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create an insert statement Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 6:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 2, 2010 6:52 AM
Points: 158, Visits: 52
Comments posted to this topic are about the item create an insert statement
Post #401777
Posted Monday, October 1, 2007 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, October 1, 2007 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, October 1, 2007 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, October 1, 2007 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 4, 2007 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 1,181, Visits: 2,652
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
Posted Friday, October 12, 2007 4:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
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
www.inforaise.com
Post #409897
Posted Friday, October 12, 2007 8:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
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 "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #410247
Posted Friday, October 12, 2007 9:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
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 "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #410249
Posted Tuesday, October 16, 2007 2:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 1,181, Visits: 2,652
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 »

Add to briefcase

Permissions Expand / Collapse