June 20, 2014 at 6:37 am
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
June 20, 2014 at 6:55 am
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.
June 20, 2014 at 7:56 am
Excellent!
This worked and the article is very interesting.
Thanks
June 20, 2014 at 7:58 am
Glad I could help. Thanks for the feedback.
June 20, 2014 at 8:10 am
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?
June 20, 2014 at 8:21 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2014 at 8:48 am
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!
June 20, 2014 at 8:58 am
You're welcome. Glad I jump in late and help. 😛
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply