create an insert statement

  • Comments posted to this topic are about the item create an insert statement

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

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

  • It doesn't generate a complete insert statement, just the first part.

    Also, this functionality is already built into SQL Query Analyzer.

  • It doesn't generate a complete insert statement, just the first part.

    Also, this functionality is already built into SQL Query Analyzer.

  • 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 😉

  • 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

  • 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


    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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"]

    --=====&nbspChange&nbspto&nbspan&nbspexisting&nbspdatabase&nbspto&nbsptest&nbspthis&nbspcode&nbspin

    &nbsp&nbsp&nbsp&nbspUSE&nbspNorthWind

    GO

    --=====&nbspBuild&nbspan&nbspInsert/Select&nbspfor&nbspthe&nbspgiven&nbsptable

    DECLARE&nbsp@TableName&nbspSYSNAME

    &nbsp&nbsp&nbsp&nbspSET&nbsp@TableName&nbsp=&nbsp'dbo.Employees'&nbsp--<<<<&nbspPUT&nbspTABLE&nbspNAME&nbspHERE

    DECLARE&nbsp@Insert&nbspVARCHAR(8000)

    DECLARE&nbsp@Select&nbspVARCHAR(8000)

    DECLARE&nbsp@MaxNameLength&nbspINT

    --=====&nbspPresets

    &nbsp&nbsp&nbsp&nbspSET&nbspNOCOUNT&nbspON

    &nbsp&nbsp&nbsp&nbspSET&nbspTRANSACTION&nbspISOLATION&nbspLEVEL&nbspREAD&nbspUNCOMMITTED

    &nbspSELECT&nbsp@MaxNameLength&nbsp=&nbspMAX(LEN(sc.Name))

    &nbsp&nbsp&nbspFROM&nbspdbo.SYSCOLUMNS&nbspsc,

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspdbo.SYSOBJECTS&nbspso

    &nbsp&nbspWHERE&nbspsc.ID&nbsp=&nbspso.ID

    &nbsp&nbsp&nbsp&nbspAND&nbspso.ID&nbsp=&nbspOBJECT_ID(@TableName)

    --=====&nbspMakes&nbspthe&nbspINSERT&nbspportion&nbspof&nbspthe&nbspcode

    &nbspSELECT&nbsp@Insert&nbsp=&nbspISNULL(@Insert+','+CHAR(10),'')&nbsp+&nbspSPACE(8)+sc.Name

    &nbsp&nbsp&nbspFROM&nbspdbo.SYSCOLUMNS&nbspsc,

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspdbo.SYSOBJECTS&nbspso

    &nbsp&nbspWHERE&nbspsc.ID&nbsp=&nbspso.ID

    &nbsp&nbsp&nbsp&nbspAND&nbspso.ID&nbsp=&nbspOBJECT_ID(@TableName)

    &nbsp&nbspORDER&nbspBY&nbspsc.ColID

    --=====&nbspMakes&nbspthe&nbspSELECT&nbspportion&nbspof&nbspthe&nbspcode

    &nbspSELECT&nbsp@Select&nbsp=&nbspISNULL(@Select+','+CHAR(10),'')&nbsp

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+&nbspSPACE(8)&nbsp+&nbspsc.Name&nbsp+&nbspSPACE(@MaxNameLength-LEN(sc.Name))&nbsp

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+&nbsp'&nbsp=&nbspxxxx.'&nbsp+&nbspsc.Name

    &nbsp&nbsp&nbspFROM&nbspdbo.SYSCOLUMNS&nbspsc,

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspdbo.SYSOBJECTS&nbspso

    &nbsp&nbspWHERE&nbspsc.ID&nbsp=&nbspso.ID

    &nbsp&nbsp&nbsp&nbspAND&nbspso.ID&nbsp=&nbspOBJECT_ID(@TableName)

    &nbsp&nbspORDER&nbspBY&nbspsc.ColID

    --=====&nbspDisplay&nbspthe&nbspINSERT/SELECT&nbsp(Copy&nbspfrom&nbspText&nbspResults&nbspWindow)

    &nbspSELECT&nbsp'&nbspINSERT&nbspINTO&nbsp'+@TableName+CHAR(10)+SPACE(8)+'('+CHAR(10)+@Insert+CHAR(10)+SPACE(8)+')'

    &nbspUNION&nbspALL

    &nbspSELECT&nbsp'&nbspSELECT'&nbsp+&nbspCHAR(10)&nbsp+&nbsp@Select

    &nbspUNION&nbspALL

    &nbspSELECT&nbsp'&nbsp&nbsp&nbspFROM&nbspsometablename&nbspxxxx'

    &nbspUNION&nbspALL

    &nbspSELECT&nbsp'&nbsp&nbspWHERE&nbspsomeconditions'

    [/font]

    --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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I think in the original script the trailing comma was trimmed off after.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply