copy data between 2 instances
-
select * INTO [Instance1].targetDB.dbo.TargetTable from [Instance2].SourceDB.dbo.SourceTable What I would like to do is copy the table from one instance to another. How do I specify copying from one instance to another. I have already created the database. The target is Sql 2000 and I have full control. The source is Sql 2005 and I have only select permissions. How would I forulate a correct select into statement? Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
select * INTO [Instance1].targetDB.dbo.TargetTable from [Instance2].SourceDB.dbo.SourceTable What I would like to do is copy the table from one instance to another. How do I specify copying from one instance to another. I have already created the database. The target is Sql 2000 and I have full control. The source is Sql 2005 and I have only select permissions. How would I forulate a correct select into statement? Thanks, Nick
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
It should be an insert statement Insert into [Instance1].targetDB.dbo.TargetTable select * from [Instance2].SourceDB.dbo.SourceTable Hope that helps. Ben
the problem is, how do I specify the Instances because "[Instance2].SourceDB.dbo.SourceTable" doesn't work. It has too many qualifiers. I think I add a linked server on my side. But how do I specify in one line which instance to use?
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
-
the problem is, how do I specify the Instances because "[Instance2].SourceDB.dbo.SourceTable" doesn't work. It has too many qualifiers. I think I add a linked server on my side. But how do I specify in one line which instance to use?
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Your [Instance2] includes the sqlserver name right? You know it might just be easier to DTS the table that is the way I do most of my table moving. Then you don't need to worry about link servers or any of that. It is the Import export Data. If you have access to sql server 2005 management studio, you go to the database you want to export a table from right click -> tasks -> export data. Then you just need to enter your destination sql server and database. Then select the table you want to copy. Hope that helps. Ben
-
the problem is, how do I specify the Instances because "[Instance2].SourceDB.dbo.SourceTable" doesn't work. It has too many qualifiers. I think I add a linked server on my side. But how do I specify in one line which instance to use?
-------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
You need to link the two instances together before you can do that. See: sp_addlinkedserver in the books online.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website