Copy data between two databases periodically [duplicate]

Question

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

Answer 1

Asuming you have got 2 Tabeles 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 possebility would be running a job in intervalls from the task sceduler 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.