November 14, 2022 at 1:37 am
I have 2 tables in my database.
One contains data which is 24 hours old.
Second contains data that were modified / added in the last 12 hours.
I take the data from the second table check in the first table to see whether any existing record was updated (using field updateddatetime greater than the one in the first table). If so, I delete that row in the first table and insert the modified row from the second table. If it is a new row, I just simply insert it into the first table.
I have a stored procedure that handle this using a cursor.
I was wondering whether I could get rid of the cursor and use a query to update the first table if data was changed or added?
I am going to simulate the issue by including two table here.
Table: DWReport in the data warehouse table
Table: UpdatedTable is the one coming from the third party database.
I link those 2 using VID and AcNo fields. Any help is appreciated.
--Main Table
CREATE TABLE #DWReport([VID] [varchar](100) NULL,AcNo [varchar](50), [OrdDate] [datetime] NOT NULL,
[Prg1] [varchar](10) NULL, UpdatedDateTime datetime
)
ON [PRIMARY]
insert into #DWReport values('1111','A111','2022-01-10 13:37:06.000','P1','2022-05-13 10:03:00.000')
insert into #DWReport values('1111','A222','2022-01-10 13:37:06.000','P2','2022-06-27 20:35:00.000')
insert into #DWReport values('1111','A123','2022-01-10 13:37:06.000','P3','2022-07-23 08:15:00.000')
insert into #DWReport values('2222','A455','2022-01-10 13:37:06.000','P3','2022-08-17 00:11:00.000')
drop table #DWReport
--Updated table
CREATE TABLE #MainTable([VID] [varchar](100) NULL,AcNo [varchar](50), [OrdDate] [datetime] NOT NULL,
[Prg1] [varchar](10) NULL, UpdatedDateTime datetime
)
ON [PRIMARY]
insert into #MainTable values('1111','A111','2022-01-10 13:37:06.000','NewVal1','2022-05-13 12:03:00.000')
insert into #MainTable values('1111','A123','2022-01-10 13:37:06.000','NewVal2','2022-07-23 14:15:00.000')
insert into #MainTable values('3333','A777','2022-01-10 13:37:06.000','NewRecord','2022-08-17 00:45:00.000')
drop table #MainTable
--Required final result
CREATE TABLE #t1([VID] [varchar](100) NULL,AcNo [varchar](50), [OrdDate] [datetime] NOT NULL,
[Prg1] [varchar](10) NULL, UpdatedDateTime datetime
)
ON [PRIMARY]
insert into #t1 values('1111','A111','2022-01-10 13:37:06.000','NewVal1','2022-05-13 12:03:00.000')
insert into #t1 values('1111','A222','2022-01-10 13:37:06.000','P2','2022-06-27 20:35:00.000')
insert into #t1 values('1111','A123','2022-01-10 13:37:06.000','NewVal2','2022-07-23 14:15:00.000')
insert into #t1 values('2222','A455','2022-01-10 13:37:06.000','P3','2022-08-17 00:11:00.000')
insert into #t1 values('3333','A777','2022-01-10 13:37:06.000','NewRecord','2022-08-17 00:45:00.000')
select * from #t1
drop table #t1
November 14, 2022 at 2:10 pm
Please provide Indexes for the tables to support whatever you want to do !!!
What did you try?
Why not just update what needs to be updated and insert what's new ? ( ? rows ??? )
AFAIK stay away from tSQL MERGE ! ( performance / bugs )
update 24h
set colx = [12h].colx
, ...
from #DWReport [24h]
inner join #t1 [12h]
on [12h].[VID] = [24h].VID
and [12h].AcNo = [24h].AcNo
and [24h].UpdatedDateTime < [12h].UpdatedDateTime
Insert into #DWReport
Select *
from #t1 [12h]
left join #DWReport [24h]
on [12h].[VID] = [24h].VID
and [12h].AcNo = [24h].AcNo
where [24h].[VID] is null ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 16, 2022 at 12:51 am
I just tried to simulate the issue. So didn't create any indexes. Indexes are on VID, AcNo and Orddate
It will be easier to delete and insert a row than update it, because there are close to 200 fields that could be updated by a user.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply