MS SQL Server 2005 Copy data from one table to another

Question

Hey all, I am trying to find out how to copy data from one table to another database table. I have two connections to two different databases. Ones called comp-DEV1 and the other SQLTEST. I am currently unable to copy data from my sorce table (SQLTEST) to my destination table (comp-DEV1).

This is the error:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '-'.

Query:

 INSERT INTO comp-DEV1.EMSSQL.dbo.tblCL
 SELECT *
 FROM SQLTEST.EMSSQL.dbo.tblCL
 WHERE NOT EXISTS(SELECT * 
             FROM comp-DEV1.EMSSQL.dbo.tblCL 
             WHERE (SQLTEST.EMSSQL.dbo.tblCL.CID = comp-DEV1.EMSSQL.dbo.tblCL.CID)
             )

Any help would be great :o)

David

Answer 1

Try wrapping your database names in brackets, such as:

INSERT INTO [comp-DEV1].EMSSQL.dbo.tblCL 
 SELECT * 
 FROM SQLTEST.EMSSQL.dbo.tblCL 
 WHERE NOT EXISTS(SELECT *  
             FROM [comp-DEV1].EMSSQL.dbo.tblCL  
             WHERE (SQLTEST.EMSSQL.dbo.tblCL.CID = 
                [comp-DEV1].EMSSQL.dbo.tblCL.CID) 
             ) 

Answer 2

Run the following statement first to check that you can read the source from the destination server:

SELECT * FROM [comp-DEV1].EMSSQL.dbo.tblCL

Get that working first then you should be on your way...