August 1, 2014 at 1:49 pm
Hi All,
I have following requirement:
I have a source table which keeps data for like 1 week.Request is to keep moving that data to a new table to preserve the data for longer time and to schedule a daily job.This job will update the record in new table if it matches the source table with a specific field(id) and insert a new record if its not already present in new table.
I have been using MERGE function to achieve that..something like this.
CREATE PROCEDURE [dbo].[usp_get_data] @id nvarchar(256)
AS
BEGIN
MERGE Testdb.dbo.new_table AS TARGET
USING Testdb.dbo.orig_table AS SOURCE
ON (TARGET.id = (SELECT id
FROM Testdb.dbo.orig_table SOURCE
WHERE id= @id --Please note that the id field is not uniqueidentifier.It returns multiple rows for one value)
GROUP BY id))
WHEN MATCHED THEN
UPDATE SET TARGET.id=@id , TARGET.time =
(SELECT MAX(time) get_time
FROM Testdb.dbo.orig_table SOURCE
WHERE id=@id
GROUP BY id)
WHEN NOT MATCHED BY TARGET THEN
INSERT (id,get_time)
VALUES (@id, (SELECT MAX(time)get_time
FROM Testdb.dbo.orig_table
WHERE id=@id
GROUP BY id));
SELECT @@ROWCOUNT;
END
Can please somebody help how to achieve the requirement in loop ?
Thanks.
August 1, 2014 at 2:19 pm
Instead of passing specific @id numbers to your stored procedure, can you code the stored procedure to automatically find the ID's that need to be merged?
If not, maybe look at passing an XML string to the stored procedure.
August 1, 2014 at 3:53 pm
Hi
So i am assuming that your new table contains the id and the max time of the corresponding id in the original table.
The problem with the while loop that your are using is that nothing is updating your @id.
I will suggest not to do a while loop at all since this will in effect look at one id record at a time, rather do it in bulk.
I suggest something like this.
-- Update the records you have
update New
setnew.time = max(orig.time)
from Testdb.dbo.new_table newwith (nolock)
join Testdb.dbo.orig_table origwith (nolock)
on orig.id = new.id
-- insert those that you don't have
insert into Testdb.dbo.new_table with (tablock)
select orig.id,max(orig.time)
from Testdb.dbo.orig_tableorig with (nolock)
left join Testdb.dbo.new_table newwith (nolock)
on orig.id = new.id
where new.id is
group by orig.id
You can put this code into a stored proc as well if needed.
Please inform if this above code works for you.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply