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

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 BDco开发者_开发知识库l with ACcol='admin'.

can anyone help me in doing this?

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.

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