Insertion Query Creation of a Table in Milliseconds

  • emreguldogan

    SSC Rookie

    Points: 39

    Comments posted to this topic are about the item Insertion Query Creation of a Table in Milliseconds

  • fburch

    Grasshopper

    Points: 15

    what is this supposed to do? is there a trick for using it?

  • emreguldogan

    SSC Rookie

    Points: 39

    This query outputs another query which is used to create an insertion procedure for the table you declared in [HERE_IS_THE_NAME_OF_THE_TABLE_YOU_WANT]. Actually it helps when there are too many columns on a table. There is no a special trick to use it just run it on Query Analyzer or something that does the same thing.

  • QLD_dba

    SSC Enthusiast

    Points: 140

    Well done Grasshopper.

    This is very handy for creating SPs for a new database.

    Thanks for sharing this with the SQL community.:)

  • azuriu

    SSC Rookie

    Points: 36

    Thanks for the script!

    One minor correction:

    The join between syscolumns and systypes should be done using xusertype not xtype.

    Using xTypes, nvarchar and sysname columns will be duplicated.

  • RBarryYoung

    SSC Guru

    Points: 143327

    As someone who has written many "automation" procedures like this, this is a good first effort emreguldogan. Here are some improvements that you could make:

    1. Removal of the Cursor and the While loop.

    (you don't need them if you use one of the string aggregation tricks)

    2. Account for the possibility of spaces in the table and column names.

    (put brackets ("[..]") around the names)

    3. Actually create the procedure for the user.

    (Instead of "print", make a big string and then execute it with "EXEC(@string);".)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung

    SSC Guru

    Points: 143327

    RBarryYoung (8/30/2012)


    As someone who has written many "automation" procedures like this, this is a good first effort emreguldogan. Here are some improvements that you could make:

    1. Removal of the Cursor and the While loop.

    (you don't need them if you use one of the string aggregation tricks)

    2. Account for the possibility of spaces in the table and column names.

    (put brackets ("[..]") around the names)

    3. Actually create the procedure for the user.

    (Instead of "print", make a big string and then execute it with "EXEC(@string);".)

    If you want to see a good example of how #1 and #3 are done, take a look at this procedure: http://www.sqlservercentral.com/scripts/Administration/69737/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung

    SSC Guru

    Points: 143327

    RBarryYoung (8/30/2012)


    ...

    If you want to see a good example of how #1 and #3 are done, take a look at this procedure: http://www.sqlservercentral.com/scripts/Administration/69737/

    Hmm, actually that's a pretty complicated example, plus it uses "EXEC sp_ExecuteSql @string .." instead of "EXEC(@string)", but either works.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • charles.byrne

    SSC Veteran

    Points: 254

    Here's another way to skin the cat. Try using Common Table Expressions. I'm basically doing a recursive query and appending the values back into the columns and then getting the last row which will have all of my arguments and building the print statement with that. The recursion also works great for running totals building a list of e-mails, etc.

    While I was able to join colorder = colorder + 1, you may have to do a ROW_NUMBER() function and partition the data if you don't have a sequential column, i.e. If colorder had a break in the numbers (1,5,7,9...)

    Then make your own sequence in the first CTE or before the Recursive Query (recur):

    ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder) as SeqID

    Remember whatever you do check the execution plan.

    declare @tabloIsim varchar(50)

    declare @crlf varchar(2)

    SET @crlf = CHAR(13)+CHAR(10)

    set @tabloIsim = '[YourTable]'

    declare @spScript nvarchar(4000)

    SET @spScript = ''

    ; --CTE

    WITH MyTable as (

    select

    sc.name as SC_NAME

    ,case st.name

    when 'int' then ''

    when 'tinyint' then ''

    when 'datetime' then ''

    else '('+cast(sc.length as nvarchar(100))+')' end as SC_LEN

    ,st.name as ST_TYPE

    ,sc.colorder

    ,sc.id

    from syscolumns sc

    inner join systypes st on sc.xtype = st.xtype

    where id = object_id(@tabloIsim)

    )

    , --Next CTE

    Sp_Parts AS (

    SELECT

    id

    ,'@' + REPLACE(SC_NAME, ' ', '_') + ' ' + ST_TYPE + SC_LEN as Arg

    ,'@' + REPLACE(SC_NAME, ' ', '_') AS Parm

    ,'[' + SC_NAME + ']' AS ColName

    ,colorder

    FROM MyTable

    )

    , --Next CTE (performs a recursive query to append the values into a single column)

    Recur AS (

    SELECT

    id

    , CONVERT(NVARCHAR(MAX), Arg) AS Args

    , CONVERT(NVARCHAR(MAX), Parm) AS Parms

    , CONVERT(NVARCHAR(MAX), ColName) AS ColNames

    ,colorder

    FROM sp_Parts

    WHERE colorder = 1

    UNION ALL

    SELECT r.id

    ,(r.Args + @crlf + ', ' + s.Arg ) as sp_Args

    ,(r.Parms + @crlf + ', ' + s.Parm) as sp_Parms

    ,(r.Colnames + @crlf + ', ' + s.Colname) as sp_Colnames

    ,s.colorder

    FROM Recur r

    INNER JOIN SP_Parts s ON

    s.ID = r.ID

    AND s.colorder = r.colorder + 1

    )

    /*-- Next CTE

    Assign RowNumber via a reverse sort to make the last row = 1.

    The last row will contain all of the

    Arguments, columns, params in the 3 columns with a CRLF and comma

    Between the rows.

    */

    , --Final CTE to Select From

    AllTheArgs AS (

    SELECT

    id

    ,Args

    ,Parms

    ,ColNames

    ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder DESC) as AllArgs

    FROM Recur

    )

    --SELECT * FROM AllTheArgs

    SELECT @spScript =

    'CREATE PROC [dbo].[ins_' + REPLACE(@tabloIsim,'[','')

    + @crlf

    + ' ' + ARGS

    + @crlf

    + 'AS'

    + @crlf

    + 'Insert INTO ' + @tabloIsim + '('

    + @crlf

    + ' ' + ColNames + ')'

    + @crlf + 'values ('

    + @crlf

    + ' ' + Parms

    + @crlf

    + ')' + @crlf

    + 'GO '

    FROM AllTheArgs

    WHERE AllArgs = 1

    PRINT @spScript --TextView will render it properly

    Note: I just read the Barry's post. Good catch on the spaces.

    My script also didn't account for spaces in the column names, but it has been modified. So in Sp_Parts I changed as follows:

    -- From this:

    -- ,'@' + SC_NAME + ' ' + ST_TYPE + SC_LEN as Arg

    -- ,'@' + SC_NAME AS Parm

    -- ,SC_NAME AS ColName

    -- To This:

    ,'@' + REPLACE(SC_NAME, ' ', '_') + ' ' + ST_TYPE + SC_LEN as Arg

    ,'@' + REPLACE(SC_NAME, ' ', '_') AS Parm

    ,'[' + SC_NAME + ']' AS ColName

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

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

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