how can i insert data from one server to another
-
insert
into DMO1\DMO1(Server)..Employees(DB)..Employee(table)
select * from Employee where empid='729'
-
Sure...you need to make sure you have a linked server setup and permissions but that should work, just fix the syntax:
insert into [DMO1\DMO].Employees.Employee
select * from Employee where empid='729'
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Ben is correct, add a linked server and simply use an insert, however, Ben's syntax isn't quite right. The following is though:
insert [DMO1\DMO].Employees.dbo.Employee
select * from Employee where empid='729'
Assuming the following:
Server name: [DMO1\DMO]
Database name: Employees
Object owner: dbo
Table name: Employee
You will have to use 4 part names when crossing server boundaries.
-
I would also suggest using the column list to guarantee the data is inserted properly. Otherwise you can have problems if the columns are not in the same order, or if a column has only been added to one of the tables.
insert [DMO1\DMO].Employees.dbo.Employee (Col001, Col002...)
SELECT Col001, Col002... FROM Employee WHERE empid='729'
Brian
-
Also, for proper functionality of linked server, you should have DCOM rights available between the 2 stations where SQL Server instances reside.
In Theory, theory and practice are the same...In practice, they are not. -
Ooops...forgot the schema!
Cheers,Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply