Eliminating matches and inserting into new table

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 2008 Forums

SQL Server Administration (2008)

Eliminating matches and inserting into new table

Author

Topic

gsqlwag
Starting Member

1 Post

Posted - 2011-09-28 : 12:45:53

I am trying to find the fastest way to create (or insert, whichever is faster) to a table that elimates any matches of account number if the accounts match in table 1 and 2. This is what I have tried, but it takes way too long being that the tables are extremely large.

SELECT F.1, F.ACCOUNT_NBR, F.2, F.3, F.4
INTO TABLE 3
FROM TABLE1 F, TABLE2 P
WHERE F.ACCOUNT_NBR <> P.account_number

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 13:09:00

yeah - that statement will result in cartesian product (cross join).

try this:


SELECT F.1, F.ACCOUNT_NBR, F.2, F.3, F.4
INTO TABLE3
FROM TABLE1 F
left outer join TABLE2 P
       on p.account_nbr = f.account_number
WHERE P.account_number is null

EDIT:
To prevent that condition add referential integrity to your database. In this case a foreign key.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 13:22:07

wont that only bring records from table1 which doesnt match with table2? what about inverse? there can be records in table 2 which is missing from table 1 also right? i think to get that you might need to add below


...
union all
SELECT F.1, F.ACCOUNT_NBR, F.2, F.3, F.4
FROM TABLE2 F
left outer join TABLE1 P
       on p.account_nbr = f.account_number
WHERE P.account_number is null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 14:07:08

Since the orginal post was only selecting from F table I figured it would only be one direction.
If those are your actual table/column names - and I hope they are not - you just need to change Visakh's ON clause to get the right column name with the right table (account_nbr vs. account_number)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources