October 31, 2013 at 9:25 am
Hey Guys,
I am trying to convert this loop to a set based query. Here is what I have so far
--LOOP
Declare
@Accountid INT
,@Typeid INT
,@users varchar(20) = 'John'
,@LoadedDate DATETIME2(7) = GETUTCDATE()
,@COUNT_LOOP INT = 1
,@COUNT INT = 0
CREATE TABLE #TEMP
(
ID INT IDENTITY(1, 1)
,AccountId INT
,TypeId INT
)
INSERT INTO #TEMP
select 11, 1 union all
select 15, 5
CREATE TABLE #EA
(
Accountid INT
,RID INT
,[date] datetime
,users varchar(20)
)
CREATE TABLE #EAA
(
Accountid INT
,RID INT
)
INSERT INTO #EAA
select 11, 10 union all
select 11, 50 union all
Select 22, 100 union all
select 33, 110
CREATE TABLE #Ramp
(
RID INT
)
INSERT INTO #Ramp
select 10 union all
select 50
SET @COUNT = (SELECT COUNT(1) FROM #TEMP)
WHILE (@COUNT_LOOP <= @COUNT)
BEGIN
SELECT @Accountid = Accountid, @Typeid = Typeid
FROM #TEMP
WHERE ID = @COUNT_LOOP;
INSERT INTO #EA
SELECT DISTINCT @Accountid
,Rid
,@LoadedDate
,@users
FROM #Ramp
DELETE FROM #EAA
WHERE RID in (
Select Rid from #Ramp)
SET @COUNT_LOOP = @COUNT_LOOP + 1
END
select * from #EA
--select * from #EAA
--select * from #Ramp
--select * from #Temp
--DROP TABLE #EA
--DROP TABLE #EAA
--DROP TABLE #TEMP
--DROP TABLE #Ramp
The result set in the #EA table is the correct thing. I am trying to do the same with a set based query using a CTE and a cross apply and the result set is
almost similar except for the first column. It doesn't look like it's looping completely. Can someone tell me what I am doing wrong? Here is the query
--SET BASED
Declare
@Accountid INT
,@Typeid INT
,@users varchar(20) = 'John'
,@LoadedDate DATETIME2(7) = GETUTCDATE()
,@COUNT_LOOP INT = 1
,@COUNT INT = 0
CREATE TABLE #TEMP
(
ID INT IDENTITY(1, 1)
,AccountId INT
,TypeId INT
)
INSERT INTO #TEMP
select 11, 1 union all
select 15, 5
CREATE TABLE #EA
(
Accountid INT
,RID INT
,[date] datetime
,users varchar(20)
)
CREATE TABLE #EAA
(
Accountid INT
,RID INT
)
INSERT INTO #EAA
select 11, 10 union all
select 11, 50 union all
Select 22, 100 union all
select 33, 110
CREATE TABLE #Ramp
(
RID INT
)
INSERT INTO #Ramp
select 10 union all
select 50
SELECT @Accountid = accountid
,@Typeid = Typeid
FROM #TEMP
;WITH CTE AS
(
Select Accountid, Typeid from #Temp
)
INSERT INTO #EA
SELECT
@accountid
,Rid
,@LoadedDate
,@users
FROM #Ramp
CROSS APPLY CTE C
DELETE FROM #EAA
--CROSS APPLY CTE C --doesn't work, is this even the right thing to do ?
WHERE RId in (
Select Rid from #Ramp)
select * from #EA
--select * from #EAA
--select * from #Ramp
--select * from #Temp
DROP TABLE #EA
DROP TABLE #EAA
DROP TABLE #TEMP
DROP TABLE #Ramp
I will also appreciate other more optimal ways of writing this query without the loop or a cursor. Afterall, a loop is a technically a cursor right 🙂
Thanks for your time.
October 31, 2013 at 9:51 am
I might be missing something, but your code seems to be doing the following:
DECLARE @users VARCHAR(20) = 'John',
@LoadedDate DATETIME2(7) = GETUTCDATE()
SELECT DISTINCT Accountid,
Rid,
@LoadedDate,
@users
FROM #TEMP
CROSS JOIN #Ramp
DELETE
FROM #EAA
WHERE RID IN (
SELECT Rid
FROM #Ramp
)
October 31, 2013 at 11:31 am
Thanks for the response,
To answer your question, Not really. The loop version returns the result set returns 4 rows, 2 for each accountid.
Accountid,RID,date,users
11,10,2013-10-31 17:21:27.203,John
11,50,2013-10-31 17:21:27.203,John
15,10,2013-10-31 17:21:27.203,John
15,50,2013-10-31 17:21:27.203,John
This is the correct answer.
My attempt at the set based version also returns 4 rows but it doesn't loop through all the accountids for some reason.
Accountid,RID,date,users
15,10,2013-10-31 17:21:27.203,John
15,50,2013-10-31 17:21:27.203,John
15,10,2013-10-31 17:21:27.203,John
15,50,2013-10-31 17:21:27.203,John
Please use the @accountid variable during the insert and not the accountid from the table directly.
Does this make sense ?
October 31, 2013 at 11:58 am
npatel565 (10/31/2013)
The loop version returns the result set returns 4 rows, 2 for each accountid.Accountid,RID,date,users
11,10,2013-10-31 17:21:27.203,John
11,50,2013-10-31 17:21:27.203,John
15,10,2013-10-31 17:21:27.203,John
15,50,2013-10-31 17:21:27.203,John
This is the correct answer.
My solution returns exactly those results. What's the problem?
npatel565 (10/31/2013)
Please use the @accountid variable during the insert and not the accountid from the table directly.
Why? there's no need to use a variable if the variable is taken from a table. You're thinking in loops not set based.
October 31, 2013 at 12:37 pm
I have to say, that was a pretty slow moment on my path. You are right, I was still thinking loop instead of set. Thanks again
Any other solutions will be accepted. I'd like to learn multiple ways of doing this.
By the way, I also found out replacing the CROSS JOIN with the CROSS APPLY function yields the same result. Is this fair to say ?
October 31, 2013 at 1:07 pm
In this case it can work the same way, but it won't always be like that. Don't let the word CROSS confuse you as the real operators are JOIN and APPLY.
Remember to always test and test again when looking for new solutions, as well as understand what the code is doing.
November 1, 2013 at 6:55 am
Good point, I have been testing a lot. One last question regarding this, what if it was an update operation in the loop,
will it be as simple as just adding the cross join to the update statements? For example if I have something like this in a loop
update #Ramp
set rid = 3
from table1 t1
join table2 t2 on t1.rid = t2.rid
To rewrite it in a set based query, will it be as simple as adding the cross join to it like this
update #Ramp
set rid = 3
from table1 t1
join table2 t2 on t1.rid = t2.rid
cross join CTE as b
November 1, 2013 at 9:30 am
I'm not sure why would you want to do a CROSS JOIN. It will duplicate rows and might give you unexpected results. If you're updating a column with a constant, then there's no reason to use a CROSS JOIN (an inner join could be used to filter rows.)
You might be oversimplifying your problem and I'm not able to see what you need.
November 1, 2013 at 9:59 am
Thanks for your response. I think you have done more than enough to help me understand set based sql more clearly.
November 1, 2013 at 10:02 am
For more information, I suggest you some more articles:
http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply