November 9, 2007 at 6:10 am
Comments posted to this topic are about the item Insertion Query Creation of a Table in Milliseconds
January 3, 2008 at 7:44 am
what is this supposed to do? is there a trick for using it?
January 3, 2008 at 8:35 am
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.
January 3, 2008 at 5:25 pm
Well done Grasshopper.
This is very handy for creating SPs for a new database.
Thanks for sharing this with the SQL community.:)
August 30, 2012 at 6:42 am
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.
August 30, 2012 at 7:29 am
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]
August 30, 2012 at 7:31 am
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]
August 30, 2012 at 7:35 am
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]
August 30, 2012 at 10:09 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply