selecting new records
-
Care to share so we might all learn something?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT *
FROM TableWithNewData
WHERE (Column1 NOT IN SELECT Column1 FROM TableWithOldData AS old_Column1)) AND
(Column2 IN (SELECT Column2 FROM TableWithOldData AS old_Column2)) AND
(Column3 IN SELECT Column3 FROM TableWithOldData AS old_Column3))
So this selects only new Column1's from TableWithNewData that is not in TableWithOldData based on the criteria of TableWithNewData.Column1 = TableWithOldData.Column1 and TableWithNewData.Column2 = TableWithOldData.Column2
Does this make sense? If it isn't correct please correct me since I'm new to SQL!
-
Hi Jennifer,
You might like to also try this:
select * from TableWithNewData n
where not exists (select 'x' from TableWithOldData o
where isnull(o.Column1,'AppropriateValueForDataType' = isnull(n.Column1,'AppropriateValueForDataType')
and isnull(o.Column2,'AppropriateValueForDataType') = isnull(n.Column2,'AppropriateValueForDataType')
...
and isnull(o.Column255,'AppropriateValueForDataType') = isnull(n.Column255,'AppropriateValueForDataType'))
That will give you all the rows from NewData that do not appear in OldData.
So, if NewData has:
Column1 Column2 Column3
------- ------- -------
A 1 John
B 2 Joe
C 3 Ted
D 4 Fred
E 5 Ahmed
And OldData has:
Column1 Column2 Column3
------- ------- -------
A 1 John
B 2 Joe
C 3 Ted
The query will return:
Column1 Column2 Column3
------- ------- -------
D 4 Fred
E 5 Ahmed
Is that what you want?
**Note. If you are new to SQL, and haven't used 'EXISTS' queries before, then the "select 'x'" part is likely to be confusing. The 'x' can be anything. eg, 1, null, 'I found a record'. It is simply because you are writing a select statement, and you have to select something.
Regards,
Matt.
-
Still another way to do this if you are using SQL 2005 is to utilize the EXCEPT operator.
DECLARE @TblNew TABLE(Column1 CHAR(1),Column2 TINYINT, Column3 VARCHAR(20))
DECLARE @TblOld TABLE(Column1 CHAR(1),Column2 TINYINT, Column3 VARCHAR(20))
INSERT INTO @TblNew(Column1,Column2,Column3) VALUES('A',1,'John')
INSERT INTO @TblNew(Column1,Column2,Column3) VALUES('B',2,'Joe')
INSERT INTO @TblNew(Column1,Column2,Column3) VALUES('C',3,'Ted')
INSERT INTO @TblNew(Column1,Column2,Column3) VALUES('D',4,'Fred')
INSERT INTO @TblNew(Column1,Column2,Column3) VALUES('E',5,'Ahmed')
INSERT INTO @TblOld(Column1,Column2,Column3) VALUES('A',1,'John')
INSERT INTO @TblOld(Column1,Column2,Column3) VALUES('B',2,'Joe')
INSERT INTO @TblOld(Column1,Column2,Column3) VALUES('C',3,'Ted')
-- Give me everything in the first statement,
-- that does not exist in the second.
SELECT * FROM @tblNew
EXCEPT
SELECT * FROM @TblOld
The simplicity of this method makes it desirable for me. Another way would be to use a left join. It all depends on what works best for both the programmer and the situation.
Cliff
-
Aha! I like the EXCEPT operator very much in this case. I am stuck in SQL2000-land, and have not come across this before. I see there's an INTERSECT as well. Very handy!
-
Ummm.... wouldn't a CHECKSUM or BINARY_CHECKSUM at the row level be a little easier?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It would be easier yes, but not always accurate. I've hit problems with both of these before - took ages to find the issue.
select
checksum('a')
select
checksum('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
--both 114
select
binary_checksum('a')
select
binary_checksum('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
--both 97
I've had to switch away from using these functions, which was a real shame given how easy they are to implement and understand.
Regards,
Matt.
-
strange....
i get 142 for the first 2 queries and 97 for the second set...
-
I think the different results are possibly due to collation sequence differences between our servers.
Or at least, this guy seems to think so. Who am I to disagree?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply