August 15, 2014 at 8:49 pm
hi
i have following tmp table that i am loading through SSIS package.
create table tmp_management
(memberid int,
pid int,
firstname varchar(10),
lastname varchar(10),
DOB datetime,
Flag char(1) )
create table tmp_management_loc
(pid int,
category varchar(10),
value varchar(10),
locid int,
Flag char(1))
insert into tmp_management
(memberid ,
pid ,
firstname ,
lastname ,
DOB )
select 1234,1,'frank','jazz','1983-0305'
union
select 2345,2,'kir','ref','1986-3544'
insert into tmp_management_loc
(pid ,
category ,
value )
select 1 , 'dsdsd','sdsd'
union
select 1,'rgf','thg'
union
select 2,'345','456'
now here , flag is column which i am updating in my sp , as I or U .
so here uniques combination is memberid+pid , i am checking in my final table if its not exist i am adding it.
if it exists and any of the value of any column change i need to update whole row.
my final table in SQL where i am loading data from this temp table using sp.
create table management
(tmp_managementid int identity(1,1) primary key,
memberid int,
pid int,
firstname varchar(10),
lastname varchar(10),
DOB datetime)
create table management_loc
(locid int identity(1,1),
pid int,
category varchar(10),
value varchar(10))
here i insert after checking flag is 'I'.(which i m updating in my sp)
the issue is updating tmp_management_loc .
example,
select * from management
1 ,1234,1,frank,jazz,1983-0305
select * from management_loc
1, 1 , 'dsdsd','sdsd'
2, 1,'rgf','thg'
here 1 in tmp_management_loc is pid, which is same for memberid = 1234.
so when second time file comes , it can have same meberid and pid, but one or more than column might change.
in this case i just need to update those row only which column value has changes.i m trying to use checksum sql function
to compare column change or now.and also updating locid (identity) in tmp table,
but its not updating correctly,
so for example, second times file comes , it has follwing data.
select * from tmp_management
1234,1,frank,jazz,1983-0305
select * from tmp_management_loc
1 , 'dsdsd','sdsd'
1,'xyz','thg'
so i dont need to insert any row here , as same data is available in my final table.
so i just need to update second row in my management_loc table.
but i dont have any column that uniquely identifies it.
any idea , how can i do it.
please help
August 17, 2014 at 1:11 am
Here is some code that does the job, not perfect though and leaves some room for improvements.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Staging tables */
create table dbo.tmp_management
(memberid int,
pid int,
firstname varchar(10),
lastname varchar(10),
DOB datetime,
Flag char(1) );
GO
/* NOTE: ADDED IDENTITY COLUMN */
create table dbo.tmp_management_loc
(TMP_ML_ID INT IDENTITY(1,1) NOT NULL,
pid int,
category varchar(10),
value varchar(10),
locid int,
Flag char(1));
GO
/* Destination tables*/
create table dbo.management
(tmp_managementid int identity(1,1) primary key,
memberid int,
pid int,
firstname varchar(10),
lastname varchar(10),
DOB datetime);
GO
create table dbo.management_loc
(locid int identity(1,1),
pid int,
category varchar(10),
value varchar(10));
GO
/* First batch in staging area */
insert into dbo.tmp_management
(memberid ,
pid ,
firstname ,
lastname ,
DOB )
select 1234,1,'frank','jazz','1983-03-05'
union
select 2345,2,'kir','ref','1986-05-04';
GO
insert into dbo.tmp_management_loc
(pid ,
category ,
value )
select 1 , 'dsdsd','sdsd'
union
select 1,'rgf','thg'
union
select 2,'345','456';
GO
/* Base query for management_loc */
CREATE VIEW dbo.STG_DIFF_management_loc
AS
SELECT
TL.pid
,TL.category
,TL.value
,CASE
WHEN TL.category <> ML.category THEN 'U1'
WHEN TL.value <> ML.value THEN 'U2'
ELSE NULL
END AS Flag
FROM dbo.tmp_management_loc TL
LEFT OUTER JOIN dbo.management_loc ML
ON TL.pid = ML.pid
WHERE TL.category = ML.category OR TL.value = ML.value
UNION ALL
SELECT DISTINCT
TL.pid
,TL.category
,TL.value
,'I' AS Flag
FROM dbo.tmp_management_loc TL
LEFT OUTER JOIN dbo.management_loc ML
ON TL.pid = ML.pid
WHERE TL.TMP_ML_ID NOT IN ( SELECT
TL.TMP_ML_ID
FROM dbo.tmp_management_loc TL
LEFT OUTER JOIN dbo.management_loc ML
ON TL.pid = ML.pid
WHERE TL.category = ML.category OR TL.value = ML.value)
GO
/* SELECT * FROM dbo.STG_DIFF_management_loc */
/* Base query for management */
CREATE VIEW dbo.STG_DIFF_management
AS
SELECT
TM.memberid
,TM.pid
,TM.firstname
,TM.lastname
,TM.DOB
,CASE
WHEN M.memberid IS NULL THEN 'I'
WHEN TM.pid <> M.pid THEN 'U'
WHEN TM.firstname <> M.firstname THEN 'U'
WHEN TM.lastname <> M.lastname THEN 'U'
WHEN TM.DOB <> M.DOB THEN 'U'
ELSE NULL
END AS Flag
FROM dbo.tmp_management TM
LEFT OUTER JOIN dbo.management M
ON TM.memberid = M.memberid
GO
/* Insert management_loc Newcomers */
INSERT INTO dbo.management_loc(pid,category,value)
SELECT ML.pid,ML.category,ML.value FROM dbo.STG_DIFF_management_loc ML
WHERE ML.Flag = 'I';
GO
/* Insert management Newcomers */
INSERT INTO dbo.management (memberid,pid,firstname,lastname,DOB)
SELECT
TM.memberid,TM.pid,TM.firstname,TM.lastname,TM.DOB
FROM dbo.STG_DIFF_management TM
WHERE TM.Flag = 'I';
GO
/* Update management_loc.value
SELECT * FROM dbo.STG_DIFF_management_loc WHERE Flag = 'U1'
SELECT * FROM dbo.STG_DIFF_management_loc WHERE Flag = 'U2'
*/
UPDATE ML
SET ML.value = SL.value
FROM dbo.STG_DIFF_management_loc SL
INNER JOIN dbo.management_loc ML
ON SL.pid = ML.pid
AND SL.category = ML.category
WHERE SL.Flag = 'U2';
GO
/* Update management_loc.category */
UPDATE ML
SET ML.category = SL.category
FROM dbo.STG_DIFF_management_loc SL
INNER JOIN dbo.management_loc ML
ON SL.pid = ML.pid
AND SL.value = ML.value
WHERE SL.Flag = 'U1';
GO
/* Update management */
UPDATE M
SET M.pid = S.pid
,M.firstname = S.firstname
,M.lastname = S.lastname
,M.DOB = S.DOB
FROM dbo.STG_DIFF_management S
INNER JOIN dbo.management M
ON S.memberid = M.memberid
WHERE S.Flag = 'U';
GO
/* Inspect the results */
SELECT * FROM dbo.tmp_management ;
SELECT * FROM dbo.tmp_management_loc ;
SELECT * FROM dbo.management ;
SELECT * FROM dbo.management_loc ;
GO
/* Next batch */
TRUNCATE TABLE dbo.tmp_management ;
TRUNCATE TABLE dbo.tmp_management_loc ;
GO
INSERT INTO dbo.tmp_management_loc (pid,category,value)
SELECT 1 ,'dsdsd','sdsd'
UNION
SELECT 1,'rgf','XXX'
UNION
SELECT 1,'AAA','BBB';
GO
INSERT INTO dbo.tmp_management (memberid,pid,firstname,lastname,DOB)
SELECT 1234,1,'frank','jazz','1983-03-05';
GO
/* Next update/insert round */
/* Insert management_loc Newcomers */
INSERT INTO dbo.management_loc(pid,category,value)
SELECT ML.pid,ML.category,ML.value FROM dbo.STG_DIFF_management_loc ML
WHERE ML.Flag = 'I';
GO
/* Insert management Newcomers */
INSERT INTO dbo.management (memberid,pid,firstname,lastname,DOB)
SELECT
TM.memberid,TM.pid,TM.firstname,TM.lastname,TM.DOB
FROM dbo.STG_DIFF_management TM
WHERE TM.Flag = 'I';
GO
/* Update management_loc.value */
UPDATE ML
SET ML.value = SL.value
FROM dbo.STG_DIFF_management_loc SL
INNER JOIN dbo.management_loc ML
ON SL.pid = ML.pid
AND SL.category = ML.category
WHERE SL.Flag = 'U2';
/* Update management_loc.category */
UPDATE ML
SET ML.category = SL.category
FROM dbo.STG_DIFF_management_loc SL
INNER JOIN dbo.management_loc ML
ON SL.pid = ML.pid
AND SL.value = ML.value
WHERE SL.Flag = 'U1';
GO
/* Update management */
UPDATE M
SET M.pid = S.pid
,M.firstname = S.firstname
,M.lastname = S.lastname
,M.DOB = S.DOB
FROM dbo.STG_DIFF_management S
INNER JOIN dbo.management M
ON S.memberid = M.memberid
WHERE S.Flag = 'U';
GO
/* Inspect the results */
SELECT * FROM dbo.tmp_management ;
SELECT * FROM dbo.tmp_management_loc ;
SELECT * FROM dbo.management ;
SELECT * FROM dbo.management_loc ;
GO
/* Clean up */
DROP VIEW dbo.STG_DIFF_management ;
DROP VIEW dbo.STG_DIFF_management_loc;
DROP TABLE dbo.tmp_management ;
DROP TABLE dbo.tmp_management_loc ;
DROP TABLE dbo.management ;
DROP TABLE dbo.management_loc ;
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply