January 24, 2011 at 12:24 am
CREATE TABLE [dbo].[emp_stage](
[empid] [smallint] NULL,
[ename] [varchar](7) NULL,
[rowid] [int] IDENTITY(1,1) NOT NULL,
[status] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Emp_dest](
[empid] [smallint] NULL,
[ename] [varchar](7) NULL,
[rowid] [int] IDENTITY(1,1) NOT NULL,
[status] [char](1) NULL
) ON [PRIMARY]
insert into Emp_dest(empid,ename,status) values (1,'A','N')
insert into Emp_dest (empid,ename,status) values (2,'B','N')
insert into Emp_dest(empid,ename,status) values (3,'C','N')
insert into Emp_dest(empid,ename,status) values (4,'D','N')
insert into Emp_dest(empid,ename,status) values (5,'E','N')
insert into emp_stage(empid,ename,status) values (6,'F','N')
insert into emp_stage(empid,ename,status) values (7,'G','N')
insert into emp_stage(empid,ename,status) values (8,'H','N')
insert into emp_stage(empid,ename,status) values (9,'I','N')
insert into emp_stage(empid,ename,status) values (1,'A','N')
insert into emp_stage(empid,ename,status) values (2,'J','N')
insert into emp_stage(empid,ename,status) values (3,'K','N')
select * from emp_stage
select * from Emp_dest
Expected Output:
I want to update emp_dest table by comparing with the emp_stage table columns that is
Condition:
in My emp_stage,emp_dest table there are thousands of rows and many columns so cannot use joins,where clauses as it decreases the performance.
Emp_dest
empid ename status
1 A NULL
2 J N
3 K N
4 D D
5 E D
6 F N
7 G N
8 H N
9 I N
January 24, 2011 at 12:37 am
Hi,
I can send answer for one of ur requirement
update emp_dest
set status='D'
where empid not in(select empid from emp_stage)
It updates 4,5 to 'D'
u can use delete,insert into,update statements to to do the remaining tasks.
January 24, 2011 at 1:07 am
Ya.. But how can we do the same with 1,2,3 empid's.
January 24, 2011 at 3:42 am
As u have posted it in 2008 forums, i assume u have SQL 2008 in your machine. This following code will do what u wanted (uses MERGE statement)
--BEGIN TRAN
MERGE Emp_dest AS Target
USING (SELECT empid,ename,status FROM emp_stage) AS Source
ON ( Target.empid = Source.empid)
WHEN MATCHED THEN
UPDATE
SET Target.empid = ISNULL (Target.empid , Source.empid)
,Target.ename = CASE
WHEN Source.ename <> Target.ename THEN Source.ename
ELSE ISNULL ( Target.ename , Source.ename)
END
,Target.status = CASE
WHEN Source.empid IS NULL THEN 'D'
WHEN (Target.empid = Source.empid AND Source.ename = Target.ename) THEN NULL
ELSE 'N'
END
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid,ename,status)
VALUES (Source.empid, Source.ename, Source.status)
WHEN NOT MATCHED BY Source THEN
UPDATE set Target.status = 'D'
-- commit or rollback
January 24, 2011 at 4:29 am
Thanks Very much
January 24, 2011 at 4:37 am
Hi,
as u mentioned the merge option works under sql server 2008 only.How can we do the above scenerio in sql server 2005..
January 24, 2011 at 4:43 am
2 options that i could think of:
Option 1 : By using multiple statement , one for INSERT of new records and UPDATE for old records
Option 2 : Put data from emp_dest into a temp table (done just for fail-over scenario), delete/truncate emp_dest , use FULL OUTER JOIN between emp_stage and emp_dest and INSERT into emp_dest
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply