By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Copy table from one SQL database to another SQL database

P: 1
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
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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

P: 4
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.