loading data from table1 and table2 of one sql database to another

shmz
shmz used Ask the Experts™
on
Hi,
This is just a general question I am asking. I like to know the solutions in both SQL server 2000 and SQL server 2005.
Please let me know about diff options of using DTS or stored proc or Scripts and relevant t-sql.

A. I like to load sales and customer data from one database to another database. databases are on the same server, same instance.

B. I like to load sales and customer data from one database to another database. databases are on the same server, different instances.

C. I like to load sales and customers data from one database to another. DBs are on different servers.

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
A.  Use three-part naming convention for your FROM clause in your SQL statements, as in:
FROM databasename.databaseowner.tablename

B. & C.  Create a linked server and use four-part naming for your FROM clause in your SQL statements, as in:
FROM linkedservername.databasename.databaseowner.tablename
Database Administrator
Commented:
Option A is the easiest.  Just ensure permissions are set in the source DB for the user that will connect to the Destination DB.  Then use insert or Select ... into statements as needed.  The statements can be scripted into SQL Agent Jobs that can run updates as needed (no DTS or SSIS required unless you need some higher functionality than you can put in an agent job or stored procedure).  This is true for SQL 2000, 2005, and 2008.
Option B and C will both require more work to get to the source.
  • A linked server would allow a simple query as in option A (but you will need to use the fully qualified object names).  This is true for SQL 2000, 2005, and 2008, and can be built as SQL Agent jobs and Stored Procs as in A.
  • Alternatively, DTS (SQL2000) will allow connections to be established between the source and destinations and tasks built that will Extract, Translate (if needed), and Load the data.
  • Or, SSIS (SQL 2005 & 2008) will do similarly with many more options (however it has a steep learning curve).  
So much depends on what exactly you are looking to do and how robust you need to build it.  
-G
G GodwinDatabase Administrator

Commented:
Have you made any progress, or do you have questions about this?
-G

Author

Commented:
Many Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial