SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by emreguldogan / Insertion Query Creation of a Table in Milliseconds / Latest Posts

RE: Insertion Query Creation of a Table in Milliseconds

charles.byrne — Thu, 30 Aug 2012 10:09:51 GMT

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 SeqIDRemember whatever you do check the execution plan. [code="sql"]declare @tabloIsim varchar(50)declare @crlf varchar(2)SET @crlf = CHAR(13)+CHAR(10)set @tabloIsim = '[YourTable]'declare @spScript nvarchar(4000)SET @spScript = ''; --CTEWITH 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 CTESp_Parts AS ( SELECT id ,'@' + REPLACE(SC_NAME, ' ', '_') + ' ' + ST_TYPE + SC_LEN as Arg ,'@' + REPLACE(SC_NAME, ' ', '_') AS Parm ,'[' + SC_NAME + ']' AS ColName ,colorderFROM 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 = 1UNION 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 FromAllTheArgs AS ( SELECT id ,Args ,Parms ,ColNames ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder DESC) as AllArgs FROM Recur) --SELECT * FROM AllTheArgsSELECT @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 AllTheArgsWHERE AllArgs = 1PRINT @spScript --TextView will render it properly[/code]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:[code="sql"]-- 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 [/code]

RE: Insertion Query Creation of a Table in Milliseconds

RBarryYoung — Thu, 30 Aug 2012 07:35:45 GMT

[quote][b]RBarryYoung (8/30/2012)[/b][hr]...If you want to see a good example of how #1 and #3 are done, take a look at this procedure: [url]http://www.sqlservercentral.com/scripts/Administration/69737/[/url][/quote]Hmm, actually that's a pretty complicated example, plus it uses "EXEC sp_ExecuteSql @string .." instead of "EXEC(@string)", but either works.

RE: Insertion Query Creation of a Table in Milliseconds

RBarryYoung — Thu, 30 Aug 2012 07:31:46 GMT

[quote][b]RBarryYoung (8/30/2012)[/b][hr]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);".)[/quote]If you want to see a good example of how #1 and #3 are done, take a look at this procedure: [url]http://www.sqlservercentral.com/scripts/Administration/69737/[/url]

RE: Insertion Query Creation of a Table in Milliseconds

RBarryYoung — Thu, 30 Aug 2012 07:29:14 GMT

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

RE: Insertion Query Creation of a Table in Milliseconds

azuriu — Thu, 30 Aug 2012 06:42:17 GMT

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.

RE: Insertion Query Creation of a Table in Milliseconds

qld_dba — Thu, 03 Jan 2008 17:25:15 GMT

Well done Grasshopper.This is very handy for creating SPs for a new database.Thanks for sharing this with the SQL community.:)

RE: Insertion Query Creation of a Table in Milliseconds

emreguldogan — Thu, 03 Jan 2008 08:35:33 GMT

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.

RE: Insertion Query Creation of a Table in Milliseconds

fburch — Thu, 03 Jan 2008 07:44:27 GMT

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

Insertion Query Creation of a Table in Milliseconds

emreguldogan — Fri, 09 Nov 2007 06:10:18 GMT

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