Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Possible Duplicate:
How to update one database from another?

I need to know how to copy data across to another SQL Server database periodically. Both databases are in the same SQL server; I think I need something similar to MySQL events (database level) or a cron job.

I want to:

  1. Copy data from database1..sametable to database2..sametable using a SQL command
  2. Run this statement every 15 minutes
share|improve this question

marked as duplicate by Mark Storey-Smith, Paul White, RolandoMySQLDBA, ypercubeᵀᴹ, Aaron Bertrand Dec 18 '12 at 14:15

This question was marked as an exact duplicate of an existing question.

Sounds like you need to figure out what SQL command is and then set up a job using SQL Server Agent. – Aaron Bertrand Dec 16 '12 at 23:06
Please edit the question to explain why you need to do this. There are many ways to achieve it, if it is truly the best solution to whatever the real problem is. The more background and context you give in the question, the better the answers will be. – Paul White Dec 17 '12 at 3:11
What is the size of the table? – Roi Gavish Dec 17 '12 at 8:10

Assuming you have got 2 Tables with identical Structure in databases

e.g. test and test1 on the same Server

By example:

    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [ID] ASC


You could use a trigger like that on database test1

   ON  [dbo].[AA] 

    Delete test2.dbo.AA from deleted where test2.dbo.AA.ID=deleted.ID 

    Set identity_insert test2.dbo.AA ON 
    insert into test2.dbo.AA (ID,NAME) select inserted.ID,Inserted.Name from
    left join test2.dbo.AA a on a.ID=inserted.ID
    where a.ID is null
    Set identity_insert test2.dbo.AA OFF

    if exists(select * from deleted)    
    Update test2.dbo.AA  Set Name=inserted.Name 
    from inserted
    where test2.dbo.AA.ID=inserted.ID


Another possibility would be running a job in intervals from the task scheduler using "sqlcmd", for example calling a procedure which was created by you for synchronizing data.

If you are using a version with "SQL Server Agent" (not contained in express versions) you could plan task here as simple query or as dts(x) jobs.

share|improve this answer
What's that last section in the trigger for? The trigger idea also fails if TRUNCATE TABLE AA is executed in the source database. It also doesn't meet the requirement to copy once every 15 minutes. – Paul White Dec 17 '12 at 3:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.