May 20, 2015 at 5:39 am
Ok littlle brainteaser I'm kind stuck on
Simplified version of the scenario
I got 2 tables Computers & NewComputers, on a weekly basis an export via csv file will be done to NewComputers
In the example simplified version I have computername,serial,old serial & name in the AD
Computers also has a bit field to indicate whether the Computer is in use 1 or in the warehouse 0
create table dbo.Computers
(
CName varchar(2),
CSerial char(1),
OSerial char(1),
CADName varchar(3),
Usage bit
)
create table dbo.NewComputers
(
CName varchar(2),
CSerial char(1),
OSerial char(1),
CADName varchar(3)
)
Simplified data
insert into dbo.Computers
values
('R0','0','','R00',1),
('R1','A','','R1A',1),
('R2','B','','R2B',1),
('R3','C','','R3C',1),
('R4','D','','R4D',1),
('R6','F','','R6F',1)
insert into dbo.NewComputers
values
('R1','B','','R1A'),
('R2','A','','R2B'),
('R5','E','','R5E'),
('R6','H','','R6H')
Then the data needs to end up in computers
New computers just need to be added.
Computers that are already present need to get a new record;the OSerial field needs to get the value from CSerial out of the existing record.
The existing record needs it's usage to be set on 0
In case Serials are just switched between 2 computers an update needs to happen where CSerial gets updated & OSerial gets the value of CSerial before the update
So in the case of the data mentioned above the following result I need to see
--Expected Results Computers
('R0','0','','R00',1)
('R1','B','A','R1A',1)
('R2','A','B','R2B',1)
('R3','C','','R3C',1)
('R4','D','','R4D',1)
('R5','E','','R5E',1)
('R6','F','','R6F',0)
('R6','H','F','R6H',1)
Now when I wrote this post I realised a MERGE should be the best solution,but I'm stuck in how I can see the difference between 'update' or 'switch' records
May 29, 2015 at 6:45 am
oaky I think I might have a solution for my little brainteaser
I thought first to use slowly changing dimension in ssis, which worked fine untill I started applying all the scenarios I had to have in the final product to it,then it slowly turned into a slowly changing package that always saw new records.
So what I did is the following
insert into #DIF_Records
select CName,CSerial,OSerial,CADName,Usage
from dbo.NewComputers
except
select CName,CSerial,OSerial,CADName,Usage
from dbo.Computers
This gives me #DIF_Records only the records that have 'any' change.
I then went through each of my scenarios,putting the result for that scenario into a temp table.
and then removing the records for those out of #DIF_Records
When all scenarios are done I do the necessary instructions to get the end results,it's messy but it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply