Find differences between two tables with no ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find differences between two tables with no ID

Author

Topic

medtech26
Posting Yak Master

169 Posts

Posted - 2009-11-06 : 14:14:07

I get third party data on a weekly basis and need to update internal data accordingly. The data contain three text columns with NO unique identifier (no duplicates for all three together).

At this stage I have two tables with three columns and I would like to:
1. Add to table_A all records from table_B that doesn't exist in Table_A.
2. Remove from table_A all records that doesn't exist in table_B.

table_A and table_B structure:


CREATE TABLE [table_A](
	[first] [nvarchar](300) NOT NULL DEFAULT (''),
	[second] [nvarchar](300) NOT NULL DEFAULT (''),
	[third] [nvarchar](300)  NOT NULL DEFAULT ('')
) ON [PRIMARY]

Currently I'm working with:


INSERT INTO table_A (first, second, third) 
SELECT first, second, third 
FROM table_B
WHERE first+second+third NOT IN (SELECT first+second+third FROM table_A);

But it's very slow and prob. not efficient, any suggestions on how to improve this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-06 : 14:19:11

1. how often do you do this and for how long will this be done. SSIS package might be your solutions
2. Do you have an index on those three fields that will speed things up real good instead of concatenating those three fields
3. if no on those tables can you create a temp table, add index and do you thang
or try this


INSERT INTO table_A (first, second, third) 
SELECT first, second, third 
FROM table_B
EXCEPT
SELECT first, second, third FROM table_A

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

medtech26
Posting Yak Master

169 Posts

Posted - 2009-11-06 : 14:29:13

1. How often do you do this and for how long will this be done. SSIS package might be your solutions
-On a weekly basis.

2. Do you have an index on those three fields that will speed things up real good instead of concatenating those three fields
-Yes, all of the fields are indexed.

3. if no on those tables can you create a temp table, add index and do you thang or try this
-I'll try your suggestion and see if it speed things up.

Thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-11-06 : 14:56:45

quote:
Originally posted by medtech26
At this stage I have two tables with three columns and I would like to:
1. Add to table_A all records from table_B that doesn't exist in Table_A.
2. Remove from table_A all records that doesn't exist in table_B.

Finally your table A is going to have all records from table B, why not just rename table B to table A.

medtech26
Posting Yak Master

169 Posts

Posted - 2009-11-06 : 15:24:13

quote:
Originally posted by rohitkumar

quote:
Originally posted by medtech26
At this stage I have two tables with three columns and I would like to:
1. Add to table_A all records from table_B that doesn't exist in Table_A.
2. Remove from table_A all records that doesn't exist in table_B.

Finally your table A is going to have all records from table B, why not just rename table B to table A.

Because it's live (data used to generate dynamic web-pages).

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-06 : 15:27:05

i meant do you have an index that combines all of those three fields

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

medtech26
Posting Yak Master

169 Posts

Posted - 2009-11-06 : 15:35:06

quote:
Originally posted by yosiasz

i meant do you have an index that combines all of those three fields

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Nop, as if I add one it's going to exceed the 900 bytes limit.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-11-06 : 15:51:41

quote:
Originally posted by medtech26

quote:
Originally posted by rohitkumar

quote:
Originally posted by medtech26
At this stage I have two tables with three columns and I would like to:
1. Add to table_A all records from table_B that doesn't exist in Table_A.
2. Remove from table_A all records that doesn't exist in table_B.

Finally your table A is going to have all records from table B, why not just rename table B to table A.

Because it's live (data used to generate dynamic web-pages).

I understand its live data but how much time is SP_RENAME going to take? much less than insert and delete operations combined...why you dont want to rename?

medtech26
Posting Yak Master

169 Posts

Posted - 2009-11-06 : 17:46:50

Sure, it's going to take MUCH LESS time but still, it's not going to be available for that amount of time. On the other hand, the combined operations are going to take long time but keep the site alive during the process.

To be honest, I don't know which is better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources