SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insertion Query Creation of a Table in Milliseconds


Insertion Query Creation of a Table in Milliseconds

Author
Message
emreguldogan
emreguldogan
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 11
Comments posted to this topic are about the item Insertion Query Creation of a Table in Milliseconds
fburch
fburch
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
what is this supposed to do? is there a trick for using it?
emreguldogan
emreguldogan
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
qld_dba
qld_dba
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 55
Well done Grasshopper.

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

Thanks for sharing this with the SQL community.Smile
azuriu
azuriu
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55214 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55214 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55214 Visits: 9518
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."
charles.byrne
charles.byrne
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search