July 9, 2003 at 3:16 am
I have 2 tables.
Table A has existing records
Table B has new records to be updated into Table A.
However, I need to avoid duplication. What would be the best way to insert the data from table B into table A, but only if the data doesnt already exist in Table A. Is there a clause/function I can use?
Thanks
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 3:37 am
When you say the data is duplicated do you mean the whole row?
July 9, 2003 at 3:40 am
Something like this might work.
Insert Into TableA
Select TableB.Col1, TableB.Col2
From TableB
Where TableB.Col1 Not In
(Select TableA.Col1 From TableA)
HTH.
Growing old is mandatory, growing up is optional
July 9, 2003 at 3:41 am
not essentially the whole row, but for example a row containing customer details with forename, surname, dob etc...
the forename and surname cannot be duplicated but obviously many people may have the same dob, so i need to run a check on all 3. if all 3 are the same then i dont want to copy the record over
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 3:46 am
If I do the following:
Insert Into TableA
Select TableB.Col1, TableB.Col2
From TableB
Where TableB.Col1 Not In
(Select TableA.Col1 From TableA) And
Where TableB.Col2 Not In
(Select TableA.Col2 From TableA)
will the AND clause mean 'and' or will it be understood as 'or' by the compiler...?
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 3:47 am
It should mean AND. I've been trying this and it appears to work ok. Just try it on a test table first.
Growing old is mandatory, growing up is optional
July 9, 2003 at 4:16 am
FIrst off to ensure absolutely no duplicates create a Unique Constraint on the table that includes all columns together that must be unique such as First Name with Last Name and Address.
Next try using NOT EXISTS as oppossed to the two NOT INs like so
Insert Into TableA
Select TableB.Col1, TableB.Col2
From TableB
WHERE NOT EXISTS (
SELECT 1 FROM TableA WHERE TableA.Col1 = TableB.Col1 AND TableA.Col2 = TableB.Col2
)
July 9, 2003 at 5:11 am
How exactly does using NOT EXISTS make it more robust? (Im not saying your wrong, just like to understand it ;))
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 5:42 am
The logic is this.
NOT EXISTS checks for the return of any value not what is in the value. But by taking and doing "WHERE TableA.Col1 = TableB.Col1 AND TableA.Col2 = TableB.Col2" I limit only to return rows when there is a match for both columns so I actually have a single subquery to compare has results true/false.
With NOT IN the way you are doing all records are returned into memory for two different subqueries. Which means each run it will may depending on the cache available have to requery the data and the server has to hash compare the data from top to bottom to make sure not in the resultset. You could potentially do a WHERE TableA.x = TableB.x in the NOT IN queries to limit but then you have two different queries that are interdependent on each other for the true condition.
July 9, 2003 at 7:52 am
ive inputted the following:
insert into customer
select * from clnpers
WHERE NOT EXISTS (
SELECT * FROM customer WHERE
customer.cust_id = clnpers.cust_id
AND customer.rec_fname = clnpers.rec_fname
AND customer.rec_sname = clnpers.rec_sname
AND customer.rec_bday = clnpers.rec_bday)
and the following error appears relating to the very first reference to the table 'customer' in the first insert statement:
'Error 213: Insert Error: Column name or number of supplied values does not match table definition'
any ideas why? Its driving me insane!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 8:01 am
Hi Andrew,
quote:
ive inputted the following:insert into customer
select * from clnpers
WHERE NOT EXISTS (
SELECT * FROM customer WHERE
customer.cust_id = clnpers.cust_id
AND customer.rec_fname = clnpers.rec_fname
AND customer.rec_sname = clnpers.rec_sname
AND customer.rec_bday = clnpers.rec_bday)
and the following error appears relating to the very first reference to the table 'customer' in the first insert statement:
'Error 213: Insert Error: Column name or number of supplied values does not match table definition'
any ideas why? Its driving me insane!
wothout looking deeper, I guess you can't use INSERT INTO along with SELECT *. Specify the columns!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 9, 2003 at 8:04 am
As I suspected, tried with spcific columns but still the same error..............
you know when something REALLY gets to you...;)
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 8:07 am
quote:
As I suspected, tried with spcific columns but still the same error..............
so are there any additional fields in customer you don't fill with your SELECT statement?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 9, 2003 at 8:08 am
no....no additional fields.......
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 9, 2003 at 9:42 am
I created both tables like so
CREATE TABLE customer (cust_id int, rec_fname varchar(20), rec_sname varchar(20), rec_bday datetime)
CREATE TABLE clnpers (cust_id int, rec_fname varchar(20), rec_sname varchar(20), rec_bday datetime)
put in a bit of data and run your query without issue.
Can you post the DDL of the tables (open EM and right click the tables, choose copy, then paste into notepad or something).
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply