Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
2  
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
4  
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? –  Justicator Dec 17 '12 at 8:10
add comment

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

Assuming you have got 2 Tables with identical Structure in databases

e.g. test and test1 on the same Server

By example:

CREATE TABLE [dbo].[AA](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_AA] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
) ON [PRIMARY]

GO

You could use a trigger like that on database test1

CREATE TRIGGER [dbo].[tr_AA] 
   ON  [dbo].[AA] 
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    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
    inserted 
    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

END

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.

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-147SQLCMD148-150-Part-I.htm

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
1  
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
add comment

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