comparing the values of two columns in a table in two different database in sql server

Question

i have 2 datbases A and B with tables AC and BD.table AC has a column ACcol and BD table has a column BDcol.

i want to move all the records in ACcol to BDcol with ACcol='admin'.

can anyone help me in doing this?

Answer 1

You should have a look at Sql Server Four Part Naming

• SQL Server Four-part naming
• Transact-SQL Syntax Conventions (Transact-SQL) : Multipart Names
• Using Synonyms in SQL Server 2005
• SQL SERVER – 2005 – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer

Answer 2

Assuming you have an account which has access to both databases, and the tables have the same schema, try the following :-

INSERT INTO B.dbo.BD 
SELECT * FROM A.dbo.AB a WHERE A.dbo.AB.col = 'admin'

e.g.

INSERT INTO B.dbo.BD ( col1, col2, col3 )
SELECT col1, col2, col3 FROM A.dbo.AB a WHERE A.dbo.AB.col = 'admin'

The key is to properly qualify the databases you are using.

Answer 3

Hi there,

You can insert the records that you want to bring accross in a temp table (in the source database) and then use Table Diff to copy the records from the temp table to the target table.

Following there are additional information about Table Diff.

http://www.volpet.com/

You can try a fully-functional copy for 30 days.