Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insertion Query Creation of a Table in Milliseconds Expand / Collapse
Author
Message
Posted Friday, November 9, 2007 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 26, 2009 8:12 AM
Points: 11, Visits: 11
Comments posted to this topic are about the item Insertion Query Creation of a Table in Milliseconds
Post #420471
Posted Thursday, January 3, 2008 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 3, 2008 7:43 AM
Points: 1, Visits: 1
what is this supposed to do? is there a trick for using it?
Post #438376
Posted Thursday, January 3, 2008 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 26, 2009 8:12 AM
Points: 11, Visits: 11
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.
Post #438412
Posted Thursday, January 3, 2008 5:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 7:07 PM
Points: 46, Visits: 55
Well done Grasshopper.

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

Thanks for sharing this with the SQL community.:)
Post #438687
Posted Thursday, August 30, 2012 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:10 PM
Points: 24, Visits: 62
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.
Post #1352159
Posted Thursday, August 30, 2012 7:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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);".)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1352209
Posted Thursday, August 30, 2012 7:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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/


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1352212
Posted Thursday, August 30, 2012 7:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1352216
Posted Thursday, August 30, 2012 10:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:06 PM
Points: 30, Visits: 172
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
Post #1352350
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse