September 23, 2007 at 6:24 pm
Comments posted to this topic are about the item create an insert statement
October 1, 2007 at 6:45 am
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.
October 1, 2007 at 6:46 am
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.
October 1, 2007 at 6:46 am
It doesn't generate a complete insert statement, just the first part.
Also, this functionality is already built into SQL Query Analyzer.
October 1, 2007 at 6:47 am
It doesn't generate a complete insert statement, just the first part.
Also, this functionality is already built into SQL Query Analyzer.
October 4, 2007 at 7:58 am
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 😉
October 12, 2007 at 4:07 am
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
October 12, 2007 at 8:32 pm
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. 😀 And almost perfect... my only question would be, what are you going to do with the trailing comma 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 9:14 pm
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...
[font="Courier New"]
--===== 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'
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 2:06 am
I think in the original script the trailing comma was trimmed off after.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply