SQL Server 2000 - Move one record from one db to another db

[gooseriver]

I want to take record where empname = 23 from db1 and insert it into db2. Both db have the same structure... How can I do this through a script to run through query analyzer

Database: db1
Table: table1
column: empname

to

Database: db2
Table: table1
column: empname

 

[Qik3Coder]

look at how to set up the two database servers as linked servers.

-The answer to your problem may not be the answer to your question.

 

[mrdenny]

Based on the question linked servers aren't needed. These databases appear to be in the same server.

This would be how you copy the record. You would then need to delete the record from the source table with a delete statement.

insert into db2.dbo.table1
select *
from db1.dbo.table1
where empname = 23

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qik3Coder]

I assumed, because he asked, that they weren't on the same server.

-The answer to your problem may not be the answer to your question.

 

[gooseriver]

The two databases are not on the same server

 

[mrdenny]

Then you will need to start by setting up a linked server, then using the full four part name of the object to access it.

insert into server2.db2.dbo.table1
select *
from db1.dbo.table1
where empname = 23

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com