469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

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 2787
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 suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.