Select and Input between servers

[Honyo]

Hi

Please can someone help an absolute newbie.
I currently have the following:

SQL Server 7 (BCSQL1)
Database = AVS
Table = Agent_Info
Columns = Agent_User_ID
Agent_Name
Login_Date
Logout_Time
Duration
Password

SQL Server 2000 (BCCRS1)
Database = AVS
Table = Agent_Info
Columns = Agent_User_ID
Agent_Name
Login_Date
Logout_Time
Duration
Password

I have a new SQL 2000 installation that is sitting on a different server (BCCRS1). I need to write a query that will run from the new server (BCCRS1). It will go out to the old server every hour and pull the last hours worth of data, then insert it into the new servers database.

Any suggestions? Ideas?

Regards
Honyo

 

[JamesLean]

1) Set up BCSQL1 as a linked server on BCCRS1.

2) Execute the insert using the fully-qualified name:

INSERT agent_info
SELECT * FROM bcsql1.avs.dbo.agent_info
WHERE login_date >= DATEADD(hh, -1, getdate())

--James