Compare 2 volatile tables, insert missing...

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

General SQL Server Forums

New to SQL Server Programming

Compare 2 volatile tables, insert missing...

Author

Topic

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-05-07 : 18:56:40

Hi,

I have 2 volatile tables;

The 1st volatile table is called Volatile_One and it simply has a table with 10 fields, the first column of the table is important because it has a unique account numbers.

I have another volatile table called Volatile_Two and it has the exact same type of information as the first volatile table but it was queried from the previous month... the first column has unique account numbers for that month.

So now I would like my 3rd query to look at both volatile tables... BUT I want to keep everything I have as is from Volatile _One and to insert any missing account number from Volatile_Two into Volatile_One.

The key is to compare account numbers if it notices an account number thats not in Volatile_One but its im Volatile_Two to not only give me that account number but I need the ENTIRE row with it... (ALL the column match on both tables)

I hope this is possible and if someone can help me... I think if I do a Union or right join it might do it? I just need help with the query please. thanks

Also;
I am assuming that I would need another volatile table to merge the data into one right?

Amber.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-05-07 : 20:00:51

Something like this?


INSERT INTO Volatile_One
SELECT * FROM Volatile_Two
WHERE AccountNumber NOT IN 
(SELECT AccountNumber FROM Volatile_One)

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 06:10:51

you can also use LEFT JOIN or EXISTS


INSERT INTO Volatile_One
SELECT * FROM Volatile_Two t2
LEFT JOIN Volatile_One t1
ON t1.AccountNumber = t2.AccountNumber
WHERE t1.AccountNumber IS NULL



INSERT INTO Volatile_One
SELECT * FROM Volatile_Two t
WHERE NOT EXISTS
(SELECT 1 FROM Volatile_One WHERE AccountNumber=t.AccountNumber)

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

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-05-08 : 11:37:05

Thanks guys!

I will let you know if it works.

Amber-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 11:40:48

welcome

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 18:41:07

Also

INSERT INTO Volatile_One
SELECT * FROM Volatile_Two V
INNER JOIN
(
SELECT 
FROM
(
SELECT AccountNumber, k=1 FROM Volatile_One
UNION 
SELECT AccountNumber, k=2 FROM Volatile_Two
)D
GROUP BY D.AccountNumber
HAVING COUNT(*) = 1
AND MAX(k) = 2
)T ON T.AccountNumber = V.AccountNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources