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


Improve Insert script


Improve Insert script

Author
Message
GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 558
I use this script to insert 99,999 records into a table with specific values. It takes about a minute or 2 to run. I know it can be done more effeciently. How can I change this to perform better?


DECLARE @CLIENT VARCHAR(50) = 11
DECLARE @CONCEPT VARCHAR(50) = 3
DECLARE @SEQ VARCHAR(50) = 1
DECLARE @GROUPID VARCHAR(50) = RIGHT('00' + @CLIENT,2) + RIGHT('00' + @CONCEPT,2) + RIGHT('00000' + @SEQ,5)


WHILE @SEQ <= 99999
BEGIN
INSERT INTO dbo.TBLGROUPID (GROUPID,id) VALUES (@GROUPID,NEWID())
SET @SEQ = @SEQ + 1
set @GROUPID = RIGHT('00' + @CLIENT,2) + RIGHT('00' + @CONCEPT,2) + RIGHT('00000' + @SEQ,5)
END

go



Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48097 Visits: 10844
Are you familiar with a Tally table? If not, Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/62867/ is well worth time time to read it. It will change the way you look at data.

As for your insert, I think this covers everything:

with level0 AS (SELECT 0 AS g UNION ALL SELECT 0),                    --2
level1 AS (SELECT 0 AS g FROM level0 AS a CROSS JOIN level0 AS b), --2^2 = 4
level2 AS (SELECT 0 AS g FROM level1 AS a CROSS JOIN level1 AS b), --4^2 = 16
level3 AS (SELECT 0 AS g FROM level2 AS a CROSS JOIN level2 AS b), --16^2 = 256
level4 AS (SELECT 0 AS g FROM level3 AS a CROSS JOIN level3 AS b), --256^2 = 65536
level5 AS (SELECT 0 AS g FROM level4 AS a CROSS JOIN level4 AS b), --65536^2 = 4294967296
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM level5)
INSERT INTO dbo.tblGroupID(GROUPID, id)
SELECT TOP 99999 '1103' + RIGHT('00000' + CONVERT(Varchar(8), t.N), 5), NEWID()
FROM Tally t;



To verify that I have your GroupID done correctly, you can rem out the INSERT line and run the query to see the values selected.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 558
Excellent!
This worked and the article is very interesting.

Thanks



Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48097 Visits: 10844
Glad I could help. Thanks for the feedback.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 558
Only 1 thing i just noticed.
in my loop I declare the @SEQ. I use this to give me the starting number. So if I want to start from 500, I would use this to start populating from 500. how can i do this with your query?



Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62165 Visits: 17954
GrassHopper (6/20/2014)
Only 1 thing i just noticed.
in my loop I declare the @SEQ. I use this to give me the starting number. So if I want to start from 500, I would use this to start populating from 500. how can i do this with your query?


Add a WHERE clause.


WHERE t.N >= 500



Or you could add 500 to t.N


CONVERT(Varchar(8), t.N + 500)



Either of those approaches should work just fine.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 558
I tried it the first time before replying and it gave me an error msg...i tried again now and it worked...must have fat fingered something. Thanks!



Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62165 Visits: 17954
You're welcome. Glad I jump in late and help. :-P

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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