February 8, 2012 at 1:48 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:53 am
I believe this will work:
SELECT temp.empno, temp.empname, temp.age
FROM
temp1empdtls major
INNER JOIN
temp2empdtls temp
ON major.empno = temp.empno
WHERE major.empname <> temp.empname OR major.age <> temp.age
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2012 at 1:56 am
Koen Verbeeck (2/8/2012)
I believe this will work:
Doesn't account for NULLs.
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:57 am
BEGIN TRAN
--Your sample data
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')
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')
--Actual solution
SELECT empno, empname, age
FROM (SELECT MIN(TableName) AS TableName, empno, empname, age
FROM (SELECT 'temp1empdtls' AS TableName, empno, empname, age
FROM temp1empdtls
UNION ALL
SELECT 'temp2empdtls' AS TableName, empno, empname, age
FROM temp2empdtls) tbls
GROUP BY empno, empname, age
HAVING COUNT(*) = 1) innerQ
WHERE TableName = 'temp2empdtls'
ROLLBACK
February 8, 2012 at 1:57 am
Thanks a million.. which is very simple and easy. You are a champ!!
February 8, 2012 at 2:03 am
suhailquadri (2/8/2012)
Thanks a million.. which is very simple and easy. You are a champ!!
Just so you know for sure, you only ever need one post on a topic. Posting multiple times will not get you more answers or quicker answers. In fact, I've found that many (including myself) are much more likely to ignore someone that has posted the same thing multiple times.
February 8, 2012 at 2:06 am
SQL Kiwi (2/8/2012)
Koen Verbeeck (2/8/2012)
I believe this will work:Doesn't account for NULLs.
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.*
);
You're absolutely right, I didn't think about that.
The INTERSECT is quite an elegant solution, that one is going straight into my knowledge library 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2012 at 2:13 am
Koen Verbeeck (2/8/2012)
The INTERSECT is quite an elegant solution, that one is going straight into my knowledge library
For details here, for anyone interested:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 2:20 am
SQL Kiwi (2/8/2012)
Koen Verbeeck (2/8/2012)
The INTERSECT is quite an elegant solution, that one is going straight into my knowledge libraryFor details here, for anyone interested:
Interesting read, thanks for the link.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2012 at 2:28 am
SQL Kiwi (2/8/2012)
Koen Verbeeck (2/8/2012)
I believe this will work:Doesn't account for NULLs.
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.*
);
That's pretty cool, done some brief testing and it appears to use similar IO to the solution I posted but nearly half the total execution time over a million rows (see below).
Table 'temp1empdtls'. Scan count 5, logical reads 3833, physical reads 32, read-ahead reads 55, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'temp2empdtls'. Scan count 5, logical reads 3833, physical reads 136, read-ahead reads 3649, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6826 ms, elapsed time = 3816 ms.
Table 'temp1empdtls'. Scan count 5, logical reads 3833, physical reads 22, read-ahead reads 3756, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'temp2empdtls'. Scan count 5, logical reads 3833, physical reads 4, read-ahead reads 1901, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5533 ms, elapsed time = 2349 ms.
Adding to my list of things to read up on for today, thanks 🙂
bah, quoted the wrong post. It was Paul's query I had compared with.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply