Automatically copy data from one table to another
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
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.