July 8, 2009 at 9:35 am
I have several tables broken out by years that contain a license number for each person. The person can be in each year table more than once. What I have to do is assign each license number a new unique ID. My plan was to create a new table to hold each unique license number and assign it a value based on the Identity column.
My create table query is:
CREATE TABLE [dbo].[TestID]
(
[DWID] [int] IDENTITY(1,1) NOT NULL,
[OldLicenseNumber] [int] NOT NULL
) ON [PRIMARY]
I then took the first year of data and inserted each unique ID into the new TestID table. I used DISTINCT because a person could be in the table more than once per year.
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2001
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID)
The first insert worked fine, but when I go to add more years, it never inserts any records.
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2002
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID)
I get the output of (0 row(s) affected) but I know for sure there are some different License Numbers in the 2002 data and there are some license number that are the same in both years.
Can you help me find what I am doing wrong? It seems like a fairly simple concept, but I am doing something incorrect.
July 8, 2009 at 9:41 am
i think your issue is the NOT EXISTS...
whenever you do that, You have to test for nulls, as that will screw up the EXISTS
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID WHERE OldLicenseNumber IS NOT NULL)
Lowell
July 8, 2009 at 9:51 am
Lowell (7/8/2009)
i think your issue is the NOT EXISTS...whenever you do that, You have to test for nulls, as that will screw up the EXISTS
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID WHERE OldLicenseNumber IS NOT NULL)
Hi Lowell,
Thanks for your suggestion. I added the WHERE OldLicenseNumber IS NOT NULL to my query, but I'm still getting zero records inserted. I think you are saying the original table (year 2002) might have nulls as an OldLicenseNumber, but all of the records in my year 2002 table have values for the license number. I will keep what you said in mind though.
July 8, 2009 at 10:21 am
Greetings,
For your INSERT:
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2002
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID)
You might want to change this to:
INSERT INTO TestID (OldLicensceNumber)
SELECT DISTINCT
t2.OldLicenceNumber
FROM TableFrom2002 t2
LEFT OUTER JOIN TestID td ON t2.OldLicenseNumber = td.OldLicenseNumber
WHERE td.OldLicenseNumber IS NULL
I hope this helps you.
Have a good day.
Terry Steadman
July 8, 2009 at 10:47 am
Thank you very much Terry, that worked on my sample data so I'm sure it will work on the rest. I do not know how the
LEFT OUTER JOIN TestID td ON t2.OldLicenseNumber = td.OldLicenseNumber
WHERE td.OldLicenseNumber IS NULL
part works, but it does. If someone could explain that to me I would appreciate it.
Thanks for everyone's help!
July 8, 2009 at 1:16 pm
No Idea (7/8/2009)
Thank you very much Terry, that worked on my sample data so I'm sure it will work on the rest. I do not know how theLEFT OUTER JOIN TestID td ON t2.OldLicenseNumber = td.OldLicenseNumber
WHERE td.OldLicenseNumber IS NULL
part works, but it does. If someone could explain that to me I would appreciate it.
Thanks for everyone's help!
Greetings,
The LEFT OUTER JOIN lets all of the records in TableFrom2002 still be listed. If there is a match in TestID, then the row will also contain that same TestID record. That also means that particular license number already exists in your TestID table and is not wanted. If there is no matching record in the TestID table, then that row will show all NULLs for all columns in the table row that would be from the TestID table. So, by checking for a NULL in the TestID.OldLicenseNumber column, you have the License Numbers that you have not inserted into your table yet.
ex:
TestID
ID, LicenseNumber
1, AAAAAA
2, BBBBBB
3, DDDDDD
TableFrom2002
ID, LicenseNumber
4, BBBBBB
5, CCCCCC
6, DDDDDD
Table returned from LEFT OUTER JOIN on TestID to TableFrom2002 (a is TableFrom2002, b is TestID)
a.ID, a.LicenseNumber, b.ID, b.LicenseNumber
4, BBBBBB, 2, BBBBBB
5, CCCCCC, NULL, NULL
c, DDDDDD, 3, DDDDDD
So, now you just check for the NULL in the OldLicenseNumber column from TestID. You have your wanted License Numbers. If we had used a regular JOIN, then the records in TableFrom2002 that you wanted to insert would not be there as the JOIN would rule them out. The OUTER JOIN lets them stay in the list.
Have a good day.
Terry Steadman
July 8, 2009 at 1:18 pm
No Idea (7/8/2009)
Thank you very much Terry, that worked on my sample data so I'm sure it will work on the rest. I do not know how theLEFT OUTER JOIN TestID td ON t2.OldLicenseNumber = td.OldLicenseNumber
WHERE td.OldLicenseNumber IS NULL
part works, but it does. If someone could explain that to me I would appreciate it.
Thanks for everyone's help!
Greetings,
The LEFT OUTER JOIN lets all of the records in TableFrom2002 still be listed. If there is a match in TestID, then the row will also contain that same TestID record. That also means that particular license number already exists in your TestID table and is not wanted. If there is no matching record in the TestID table, then that row will show all NULLs for all columns in the table row that would be from the TestID table. So, by checking for a NULL in the TestID.OldLicenseNumber column, you have the License Numbers that you have not inserted into your table yet.
ex:
TestID
ID, LicenseNumber
1, AAAAAA
2, BBBBBB
3, DDDDDD
TableFrom2002
ID, LicenseNumber
4, BBBBBB
5, CCCCCC
6, DDDDDD
Table returned from LEFT OUTER JOIN on TestID to TableFrom2002 (a is TableFrom2002, b is TestID)
a.ID, a.LicenseNumber, b.ID, b.LicenseNumber
4, BBBBBB, 2, BBBBBB
5, CCCCCC, NULL, NULL
c, DDDDDD, 3, DDDDDD
So, now you just check for the NULL in the OldLicenseNumber column from TestID. You have your wanted License Numbers. If we had used a regular JOIN, then the records in TableFrom2002 that you wanted to insert would not be there as the JOIN would rule them out. The OUTER JOIN lets them stay in the list.
Have a good day.
Terry Steadman
July 8, 2009 at 1:20 pm
No Idea (7/8/2009)
Thank you very much Terry, that worked on my sample data so I'm sure it will work on the rest. I do not know how theLEFT OUTER JOIN TestID td ON t2.OldLicenseNumber = td.OldLicenseNumber
WHERE td.OldLicenseNumber IS NULL
part works, but it does. If someone could explain that to me I would appreciate it.
Thanks for everyone's help!
Greetings,
The LEFT OUTER JOIN lets all of the records in TableFrom2002 still be listed. If there is a match in TestID, then the row will also contain that same TestID record. That also means that particular license number already exists in your TestID table and is not wanted. If there is no matching record in the TestID table, then that row will show all NULLs for all columns in the table row that would be from the TestID table. So, by checking for a NULL in the TestID.OldLicenseNumber column, you have the License Numbers that you have not inserted into your table yet.
ex:
TestID
ID, LicenseNumber
1, AAAAAA
2, BBBBBB
3, DDDDDD
TableFrom2002
ID, LicenseNumber
4, BBBBBB
5, CCCCCC
6, DDDDDD
Table returned from LEFT OUTER JOIN on TestID to TableFrom2002 (a is TableFrom2002, b is TestID)
a.ID, a.LicenseNumber, b.ID, b.LicenseNumber
4, BBBBBB, 2, BBBBBB
5, CCCCCC, NULL, NULL
c, DDDDDD, 3, DDDDDD
So, now you just check for the NULL in the OldLicenseNumber column from TestID. You have your wanted License Numbers. If we had used a regular JOIN, then the records in TableFrom2002 that you wanted to insert would not be there as the JOIN would rule them out. The OUTER JOIN lets them stay in the list.
Have a good day.
Terry Steadman
July 8, 2009 at 2:48 pm
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2002
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID)
Your problem is with the "not exists", you're using it incorrectly.
EXISTS vs IN
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2002
WHERE NOT EXISTS
(SELECT 1 FROM TestID t where t.OldLicenseNumber = OldLicenseNumber )
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT OldLicenseNumber
FROM TableFrom2002
WHERE NOT in (SELECT OldLicenseNumber FROM TestID t where OldLicenseNumber is not null)
OK....
#1. EXISTS will return true the instant ANYTHING is returned
eg. select 'true' where exists (select 1)
EXISTS does NOT care what is returned or how many rows, as soon as anything returns, it becomes true
#2. You need to make sure you state "where OldLicenseNumber is not null" only if that field can be null. The reason is if you have even 1 null value rerturned it will automatically make the in statement false, which will then get NOT applied and return true every time. But this isn't an issue if that column can't be null.
P.S. the EXISTS statement used above is a correlated query which means that statement is re-ran for every row. BUT, because the EXISTS statement returns true the instant anything is returned, it doesn't have to wait around for any returned rows past the first returned.
I'd recommend using the left join query someone else posted if you're doing a mass insert vs individual inserts
July 8, 2009 at 2:52 pm
Lowell (7/8/2009)
i think your issue is the NOT EXISTS...whenever you do that, You have to test for nulls, as that will screw up the EXISTS
WHERE NOT EXISTS
(SELECT OldLicenseNumber FROM TestID WHERE OldLicenseNumber IS NOT NULL)
select 'true' where exists (select null)
this returns true. exists doesn't care in the slightest what's returned, just as long as at least 1 row is returned
July 8, 2009 at 5:54 pm
You could do it using NOT EXISTS but you would need to reference the outer table in the subquery.
INSERT INTO TestID
(OldLicenseNumber)
SELECT
DISTINCT x1.OldLicenseNumber
FROM TableFrom2002 x1
WHERE NOT EXISTS
(SELECT x2.OldLicenseNumber FROM TestID x2 WHERE x1.OldLicenseNumber = x2.OldLicenseNumber )
James Leeper
Database Administrator
WDS Global - Americas Region
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply