February 8, 2012 at 1:49 am
Dear all, Please help
well i end my life with one requirement.
Scenario: I have two tables ( major emp details and temp emp details) holds employee information.
Major employee details table holds information w.r.t to specific database and temp employee details tables holds updated employee information whose data is pushed by some SSIS package.
Requirement:
i want to write such procedure which display only those employee details whose details are updated from major emp details table to temp employee details table
To test:
--create major emp dtls table which holds infor
create table temp1empdtls(empno int,empname varchar(50),age int)
insert into temp1empdtls values(11,'John','38')
insert into temp1empdtls values(12,'Eric','20')
insert into temp1empdtls values(13,'Adam','40')
select * from temp1empdtls
--create temp emp dtls table whose details are loaded by SSIS package with some datasources
create table temp2empdtls(empno int,empname varchar(50),age int)
insert into temp2empdtls values(11,'John','39')
insert into temp2empdtls values(12,'Eric-delight','22')
insert into temp2empdtls values(13,'Adam','40')
select * from temp2empdtls
--drop all tables
drop table temp1empdtls
drop table temp2empdtls
--Desired output: should be only two records as these details are updated comparitively
empno empname age
11 John 39
12 Eric-delight 22
Please help me in this!!
February 8, 2012 at 1:52 am
February 8, 2012 at 1:54 am
This meets the stated requirement:
SELECT
t2.*
FROM dbo.temp1empdtls AS t1
JOIN dbo.temp2empdtls AS t2 ON
t2.empno = t1.empno
WHERE
NOT EXISTS
(
SELECT t1.*
INTERSECT
SELECT t2.*
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 1:59 am
Thanks for the reply..
Similar post : http://qa.sqlservercentral.com/Forums/Topic1248766-391-1.aspx?Update=1
February 8, 2012 at 2:15 am
suhailquadri (2/8/2012)
Similar post : http://qa.sqlservercentral.com/Forums/Topic1248766-391-1.aspx?Update=1%5B/quote%5D
Yes I saw that after I posted here. Please don't post twice in different places next time, thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 2:45 am
Is doesn't work use 'except'
February 8, 2012 at 3:17 am
yubo1 (2/8/2012)
Is doesn't work use 'except'
You didn't try it did you? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 3:46 am
create table temp1empdtls(empno int,empname varchar(50),age int)
insert into temp1empdtls values(11,'John','38')
insert into temp1empdtls values(12,'Eric','20')
insert into temp1empdtls values(13,'Adam','40')
select * from temp1empdtls
--create temp emp dtls table whose details are loaded by SSIS package with some datasources
create table temp2empdtls(empno int,empname varchar(50),age int)
insert into temp2empdtls values(11,'John','39')
insert into temp2empdtls values(12,'Eric-delight','22')
insert into temp2empdtls values(13,'Adam','40')
select * from temp2empdtls
--drop all tables
drop table temp1empdtls
drop table temp2empdtls
select * from temp2empdtls
except
select * from temp1empdtls
/*
11 John 39
12 Eric-delight 22
*/
February 8, 2012 at 4:11 am
yubo1 (2/8/2012)
select * from temp2empdtlsexcept
select * from temp1empdtls
INSERT dbo.temp2empdtls
(empno, empname, age)
VALUES
(14, 'Bob', 46);
Your code gives the wrong results.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 10, 2012 at 2:06 pm
I feel inner join is very simple and easy to understand and good performance.. well i knew this trick to solve but hardtimes we can't solve small issues. :)..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply