Loading Questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loading Questions

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-26 : 12:34:00

I have 1 table and moving to other server. But i want to only match 1 table in other server. I mean whenever data gets loaded in one server , the same table in other server should get replicated. is there any way beside replication or create DTS package.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-26 : 12:56:30

BCP Bulk insert in a trigger?

Whats wrong with replication?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-26 : 13:53:13

sorry how do i bulk insert in a trigger?

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-26 : 13:53:47

You have a couple of options
1) use replication
2) create a linked server to your target and create a trigger on your table that populates the destination server anytime your source is updated
3) if you are on 2005 use service broker
4) create nightly jobs with ssis or dts to move the data if you don't need this information up to the second.

Any reason why you don't want to use replication?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-11-26 : 13:55:49

I can setup the linked server . Can me help on the trigger side please . Source server is 2000 and Destination server is 2005

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-26 : 15:27:58

On your source table you need to create a trigger that select from the logical inserted table and put that data into the the linked server table for any insert.

CREATE TRIGGER trTableNameI ON TableName
FOR INSERT
INSERT INTO LinkedServerName.dbo.DestinationTable (Column1, Column2)
SELECT Column1, Column2
FROM Inserted

You should read books online.

http://msdn2.microsoft.com/En-US/library/aa258254(SQL.80).aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-26 : 15:33:26

quote:
Originally posted by cr8nk

On your source table you need to create a trigger that select from the logical inserted table and put that data into the the linked server table for any insert.
...

That is probably not a good idea. That means that your transaction will fail if you cannot reach the linked server or it is down.

CODO ERGO SUM

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-26 : 16:31:28

I agree with that statment. I should rephrase my previous post, using a linked server in a trigger is the easiest option to implement, unfortunately it contains a large dependency on the linked server which will cause your inserts to fail if you linked server loses connection.

Sodeep,
I guess I go all the way back to trying to figure out why you don't want to use replication, since you are exploring alternate solutions to "replicate" data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources