472,103 Members | 1,554 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,103 software developers and data experts.

Copy table from one SQL database to another SQL database

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???
Feb 10 '10 #1
2 2862
ck9663
2,878 Expert 2GB
try this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. select * 
  3. into <targetservername>.<targetdatabasename>.<owner>.<newtablename>
  4. from <sourceservername>.<sourcedatabasename>.<owner>.<sourcetablename>
  5.  
  6.  
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
Feb 11 '10 #2
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)
Mar 11 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by lgbjr | last post: by
6 posts views Thread by sql_server_user | last post: by
4 posts views Thread by JIM.H. | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.