Copy table from one SQL database to another SQL database

[janokollar]

I would like to know whether it is possible to make script or anything like that, that would automatically (weekly, daily,...) copy data from table A from database A into table B, database B. Both tables have exactly same structure of columns,...

Both databases are on the same SQL server, both databaes are the same MS SQL 2005 databases.

How to proceedd???

[ck9663]

try this:

 
select * 
into <targetservername>.<targetdatabasename>.<owner>.<newtablename>
from <sourceservername>.<sourcedatabasename>.<owner>.<sourcetablename>
 
 

Make sure:

1. You have the necessary rights to write on the target database.
2. You have target server linked to your source server.

Happy Coding!!!

~~ CK

[raveengo]

USE OPENSOURCE U CAN INSERT OR UPDATE TABLE ONE DATABSE TO ANOHTERONE.

FOR EXAMPLE

USE PUBS

INSERT INTO Categories
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
WHERE CategoryID NOT IN (SELECT CategoryID FROM Categories)