Automatically copy data from one table to another

Question

I need to set up something to copy data from a table in one database to an identical table in another database. I've heard about setting up an automatic job in SQL, but I don't know where to begin.

Can someone explain how to do this in SQL Server 2012 using Management Studio?

Answer 1

Start here: http://msdn.microsoft.com/en-us/library/ms189237(v=sql.110).aspx

That will get you started on "what is SQL Server Agent".

As far as copying data from one table to another, if you want to run it on a schedule then you would create a SQL Agent job that would execute your SQL script/stored procedure on that schedule (this will make more sense once you read through the linked article).

As far as populating the table, you could use a number of things like:

SELECT ST.COL1, ST.COL2, ... ,ST.COLN
INTO [DESTINATION TABLE] 
FROM [SOURCE TABLE] ST

or if they are identical tables:

TRUNCATE TABLE [DESTINATION TABLE]
SELECT *
INTO [DESTINATION TABLE] 
FROM [SOURCE TABLE]

Those are general methods. If you only want to insert data into from the source table that is not already in the destination table you will need more logic to determine which records meet that criteria. This should get you started anyway.

Answer 2

You can copy the data from table from one database to other which having the same structure using select * into commands as follows.

select *

into DATABASE_NAME1.NewTable

from DATABASE_NAME2.OldTable

Answer 3

If the truncate/insert does not work for you, you can also use the MERGE instruction, as detailed here on SO: https://stackoverflow.com/a/18873941/78522

Answer 4

Have you considered using replication? Although you can certainly roll your own solution using jobs (and this may be appropriate sometimes) replication was specifically designed to implement (semi-)real time data copying.