How to Compare Tow Table Data - SQL Server Forums

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

.NET Inside SQL Server (2005)

How to Compare Tow Table Data

Author

Topic

AvinChandrasen
Starting Member

1 Post

Posted - 2008-11-28 : 11:34:09

i have two table called table a,b

it have tow columns id,stage

we have the same data in both table

like this

table a contains

id stage

1 1

1 2

1 3

2 1

2 2

table b contains

id stage

1 2

1 3

2 2

i need to inset that data in table b which is not in table b but in table a

both tables r located on different database server.

the result for table b like this as compare to table a

id stage

1 1

1 2

1 3

2 1

2 2

how could i do this ?

thanks in adwans.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 11:44:02

use OPENROWSET, then use below


INSERT INTO TableB
SELECT a.id,stage
FROM (SELECT a.*
  FROM OPENROWSET('SQLNCLI', 'Server=ServerA;Trusted_Connection=yes;',
     'SELECT id,stage
      FROM TableA') AS a
LEFT JOIN TableB b
ON b.id=a.id
AND b.stage=a.stage
WHERE b.id IS NULL

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-28 : 11:44:50

Create a linked server on the server where a is located for the server where b is located then run this on the server where a is located

INSERT servername.databasename.dbo.b (id, stage)
SELECT id, stage
FROM a
WHERE NOT EXISTS (SELECT * FROM servername.databasename.dbo.b x WHERE x.id = a.id AND x.stage = a.stage)

Or create a linked server on the server where b is located for the server where a is located then run this on the server where b is located

INSERT b (id, stage)
SELECT id, stage
FROM servername.databasename.dbo.a
WHERE NOT EXISTS (SELECT * FROM b x WHERE x.id = a.id AND x.stage = a.stage)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 11:47:12

Or Use SSIS with Lookup transformation and based on Lookup table it will insert .

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 11:49:17

See this:
http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources