November 10, 2014 at 11:46 am
I am trying to convert a RBAR procedure into a set based procedure.
A RIN in the vTable is unique for that parcel and propCode. The RIN stays the same as the data is copied forward to each new year.
The only time a RIN is modifed is on an Insert. I have a nice little SP that inserts a row into #RIN_Table and returns the next identity using SCOPE_IDENTITY. This works well enough when inserting rows one at a time.
I have a process which goes through a year and determines if penalties need to be added. If the penalty code exists in the vTable, then update it. But if the penalty code does not exist, it needs to insert it with the next available RIN.
How do I get the next available RIN from #RIN_Table when executing a Merge? I have tried each of the available function types, scalar, inline and mult-statement and each throws a error about what I can't do. (Not a syntax error, but a run time error)
These cut down tables should demonstrate the issue I am having.
IF OBJECT_ID('tempdb..#RIN_Table') IS NOT NULL drop table #RIN_Table
GO
IF OBJECT_ID('tempdb..#RIN_Table') IS NULL
begin
create table #RIN_Table
(
RIN int identity(27,1)
,MDateTime datetime
)
end
declare @mtable TABLE
(
mYear int
,Parcel varchar(13)
)
declare @vtable TABLE
(
sysID int identity (1,1)
,vYear int
,Parcel varchar(13)
,PropCode char(4)
,RIN int
,Market int
)
insert @mTable (mYear, Parcel) values
(2014,'00-0000-0001')
,(2014,'00-0000-0012')
,(2014,'00-0000-0020')
,(2015,'00-0000-0001')
,(2015,'00-0000-0012')
,(2015,'00-0000-0020')
insert @vTable (vYear, Parcel, PropCode, RIN, Market) values
(2014, '00-0000-0001', 'LR01', 1, 100)
,(2014, '00-0000-0001', 'LP01', 2, 200)
,(2015, '00-0000-0001', 'LR01', 1, 300)
,(2015, '00-0000-0012','BC05',5, 400)
,(2014, '00-0000-0012','BC05',5, 500)
,(2014, '00-0000-0012','BR03',6, 600)
,(2014, '00-0000-0012','BR03',7, 700)
,(2015, '00-0000-0012','BR03',6, 800)
,(2015, '00-0000-0012','BR03',7, 900)
,(2015, '00-0000-0012','LR01',15, 1000)
,(2014, '00-0000-0012','LR01',15, 1100)
,(2014, '00-0000-0012','LS02',17, 1200)
,(2015, '00-0000-0012','LS02',17, 1300)
,(2015, '00-0000-0020','BR01',20, 1400)
,(2014, '00-0000-0020','BR01',20, 1500)
,(2014, '00-0000-0020','BS05',21, 1600)
,(2015, '00-0000-0020','BS05',21, 1700)
,(2015, '00-0000-0020','LR01',24, 1800)
,(2014, '00-0000-0020','LR01',24, 1900)
,(2014, '00-0000-0020','LS02',26, 2000)
,(2015, '00-0000-0020','LS02',26, 2100)
Merge statement that is now putting in -1 where I would like to get the next available RIN.
MERGE @vTable V
USING
(
SELECT mYear, Parcel
FROM @mTable
WHERE mYear = 2014
) M on m.mYear = V.vYear and m.Parcel = v.Parcel and v.PropCode = 'LP01'
WHEN MATCHED THEN
UPDATE SET
V.Market = V.Market * 1.10
WHEN NOT MATCHED THEN
INSERT (vYear, Parcel, PropCode, RIN, Market)
VALUES (M.mYear, M.Parcel, 'LP01', -1, 10);
select * from @vtable where vYear = 2014 order by Parcel, propcode
IF OBJECT_ID('tempdb..#RIN_Table') IS NOT NULL DROP TABLE #RIN_Table
Thank-you!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 11, 2014 at 9:43 am
Anyone have an idea on this? :unsure:
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 11, 2014 at 10:12 am
Is the RIN required to be sequential (No Gaps)? If not, this will work and eliminates the need for the temp table to generate the identity value. I'm assuming you are getting the MAX(RIN) to set the Identity seed in the temp table, and this will eliminate that as well. You may want to look into sp_getapplock to insure that it will work in concurrent sessions without using the same values for the RIN.
IF OBJECT_ID('tempdb..#RIN_Table') IS NOT NULL
DROP TABLE #RIN_Table
GO
IF OBJECT_ID('tempdb..#RIN_Table') IS NULL
BEGIN
CREATE TABLE #RIN_Table
(
RIN INT IDENTITY(27, 1),
MDateTime DATETIME
)
END
DECLARE @mtable TABLE
(
mYear INT,
Parcel VARCHAR(13)
)
DECLARE @vtable TABLE
(
sysID INT IDENTITY(1, 1),
vYear INT,
Parcel VARCHAR(13),
PropCode CHAR(4),
RIN INT,
Market INT,
initial INT NULL
)
INSERT @mtable
(mYear, Parcel)
VALUES
(2014, '00-0000-0001')
, (2014, '00-0000-0012')
, (2014, '00-0000-0020')
, (2015, '00-0000-0001')
, (2015, '00-0000-0012')
, (2015, '00-0000-0020')
INSERT @vtable
(vYear, Parcel, PropCode, RIN, Market)
VALUES
(2014, '00-0000-0001', 'LR01', 1, 100)
, (2014, '00-0000-0001', 'LP01', 2, 200)
, (2015, '00-0000-0001', 'LR01', 1, 300)
, (2015, '00-0000-0012', 'BC05', 5, 400)
, (2014, '00-0000-0012', 'BC05', 5, 500)
, (2014, '00-0000-0012', 'BR03', 6, 600)
, (2014, '00-0000-0012', 'BR03', 7, 700)
, (2015, '00-0000-0012', 'BR03', 6, 800)
, (2015, '00-0000-0012', 'BR03', 7, 900)
, (2015, '00-0000-0012', 'LR01', 15, 1000)
, (2014, '00-0000-0012', 'LR01', 15, 1100)
, (2014, '00-0000-0012', 'LS02', 17, 1200)
, (2015, '00-0000-0012', 'LS02', 17, 1300)
, (2015, '00-0000-0020', 'BR01', 20, 1400)
, (2014, '00-0000-0020', 'BR01', 20, 1500)
, (2014, '00-0000-0020', 'BS05', 21, 1600)
, (2015, '00-0000-0020', 'BS05', 21, 1700)
, (2015, '00-0000-0020', 'LR01', 24, 1800)
, (2014, '00-0000-0020', 'LR01', 24, 1900)
, (2014, '00-0000-0020', 'LS02', 26, 2000)
, (2015, '00-0000-0020', 'LS02', 26, 2100);
MERGE @vtable V
USING
(
SELECT
M.mYear,
M.Parcel,
/* this will create gaps as ROW_NUMBER() is over the whole set including those that are updates I'm sure
there is a way to eliminate the gaps, but do not have the time to figure it out since non gaps was not
a requirement mentioned. */
MAX(V2.MaxRin) OVER () + ROW_NUMBER() OVER (ORDER BY M.mYear, M.Parcel) AS RIN
FROM
@mtable AS M /* the max RIN in the existing table so the result of ROW_NUMBER() can be added to it */
CROSS APPLY (
SELECT
MAX(newV.RIN) AS MaxRin
FROM
@vtable AS newV
) AS V2
WHERE
M.mYear = 2014
) M
ON M.mYear = V.vYear AND
M.Parcel = V.Parcel AND
V.PropCode = 'LP01'
WHEN MATCHED THEN
UPDATE SET
V.Market = V.Market * 1.10
WHEN NOT MATCHED THEN
INSERT
(
vYear,
Parcel,
PropCode,
RIN,
Market,
initial
)
VALUES (
M.mYear,
M.Parcel,
'LP01',
M.RIN,
10,
-1
);
SELECT
*
FROM
@vtable
WHERE
vYear = 2014
ORDER BY
RIN,
Parcel,
PropCode
IF OBJECT_ID('tempdb..#RIN_Table') IS NOT NULL
DROP TABLE #RIN_Table
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2014 at 10:34 am
Jack Corbett (11/11/2014)
Is the RIN required to be sequential (No Gaps)? If not, this will work and eliminates the need for the temp table to generate the identity value. I'm assuming you are getting the MAX(RIN) to set the Identity seed in the temp table, and this will eliminate that as well. You may want to look into sp_getapplock to insure that it will work in concurrent sessions without using the same values for the RIN.
Thanks for the reply!
To answer your questions, the RIN does not need to be sequential. I don't care what it is as long as it is the next unique number. The temp table, #RIN_Table, was done this way just for posting purposes. I have an actual table that is like #RIN_Table. I call a stored procedure that simply supplies the next identity with an Insert and a call to SCOPE_IDENTITY. I don't use MAX(RIN) at all.
Concurrency will be a big issue. Without the #RIN_Table, or something similar to it, how do I guarantee that the next number provided by MAX(..)+1 is unique? That is why I was hoping that there would be a solution that could call a function from within the INSERT statement that would return the next identity. Then I don't have to worry about concurrency.
Thanks again. I will study what you have posted.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 11, 2014 at 4:10 pm
How I've dealt with getting a set of unique identifiers ahead of time is to insert a dummy row into your iterating table (#RIN_Table in your example) and then use an OUTPUT clause to put all those identifiers into some local object that I can then pair up with my data.
So to add to your example, something like this:
if object_id('tempdb.dbo.#LocalRINs') is not null drop table #LocalRINs
create table #LocalRINs
(
Ident int identity(1,1) primary key clustered,
RIN int unique
)
insert into #RIN_Table (MDateTime)
output inserted.RIN
into #LocalRINs
select getdate()
from @mTable
And voila! You now have n-unique sequential integers, where n is the number of rows in @mTable. You can then perform an arbitrary join between the two tables to get your new RINs in line with your data you're about to merge
;with mtab as
(
select
Ident = row_number() over(order by (select null)),
*
from @mTable
)
select *
from mtab m
inner join #LocalRins lr
on m.Ident = lr.Ident
November 11, 2014 at 4:18 pm
November 12, 2014 at 10:37 am
JeeTee (11/11/2014)
Also, as a side note, even though this is a 2008 specific forum, if you have 2012 +, if all you need is the number and you don't care about persisting them, you can get around having to have that dummy incrementing table using a SEQUENCE object.
Tell me about it. I wish I could develop for 2012. This whole exercise would have been a trivial endeavor.
What would be real nice if MS would implement SEQUENCES and the rest of the Window Functions into 2008 R3!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 12, 2014 at 10:49 am
JeeTee (11/11/2014)
How I've dealt with getting a set of unique identifiers ahead of time is to insert a dummy row into your iterating table (#RIN_Table in your example) and then use an OUTPUT clause to put all those identifiers into some local object that I can then pair up with my data.
insert into #RIN_Table (MDateTime)
output inserted.RIN
into #LocalRINs
select getdate()
from @mTable
And voila! You now have n-unique sequential integers, where n is the number of rows in @mTable. You can then perform an arbitrary join between the two tables to get your new RINs in line with your data you're about to merge
Thank you for the idea! I was able to modify your suggestions and get it to work with my situation.
What I did was run the merge statement using the output option to send some of the columns to a temp table, #LocalRINs. This gave me the number of rows that were inserted. Now I know exactly how many RIN's I need to generate.
Then a few Deletes and Updates on the temp table and a join against the main table and everyone is happy.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply