August 26, 2009 at 9:20 am
Ok, I want to write a trigger that when data is inserted into one table on a database server, it automatically copies to a table on a different server. I know how to write the trigger, that's not the problem. My question is, on my Insert statement, will it be INSERT INTO servername.databasename.tablename? Is that how it works, or is there something else that needs to be done to be able to do this? I haven't tested it, just wanted to ask before I got started.
Thanks,
Jordon
August 26, 2009 at 9:31 am
DON'T DO IT ! At least not with a trigger !
What if "the other instance" goes in maintenance, your "primary" instance will nolonger work because that trigger will fail !
If someone holds an exclusive lock on the "other" instance, your trigger will fail because of locking !
Did you consider a SERVICE BROKER solution, db-mirroring or another subscription based replication ?
IMO the service broker solution may perfectly suit your needs.
Asynchrone, but not prone to (temporary) unavailability of the "other" instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 26, 2009 at 9:43 am
If both databases have same structure you can use replication. If not, what about a usual ETL scenario?
August 26, 2009 at 11:37 am
All I'm doing is when a new employee is created in our HR system, I want that new employee to go to another system, without having to do a manual process. I'm still learning SQL, so that's why I was thinking SQL trigger, because it won't happen that often; however, when it does, I need it to send to the other database. What would be the best avenue to do?
Jordon
August 26, 2009 at 11:44 am
Well, you should ask yourself whether it's important that this process occurs instantaneously.
If it doesn't, then what I'd suggest is that you write an SSIS package which will analyze your table periodically, and move over all the new records.
You could even have this package running every 5 minutes if you absolutely needed them quickly. The point is just that your main process is not dependent upon the moving process.
August 26, 2009 at 12:02 pm
It could happen every 24 hours for all that matters. I'll look into the SSIS package. I've used one of those before to import data and I'm sure that I can figure it out for this other stuff, but how do I schedule that to run, do I use SQL CMD and create a Windows Schedule task?
August 26, 2009 at 12:04 pm
Just looking at this, I can create a SSIS package the pulls from the one table and then post to the other table, but how do I create it where it only post new records?
August 26, 2009 at 12:20 pm
Well, actually, I should have mentioned something - SSIS can only be used as a scheduled task if you have the Enterprise version (or maybe the Developer one as well, not sure). If you have the standard version, then you can't use that method.
However, you could instead just write a SSMS job that will do it. All you'll need to do, is check to see any records which are new in your main table, and then write them to your other table.
You could do that in several ways. You could have an identity field in both tables, and then take the MAX identity value in your destination table. Then take all records from your source table which have an ID greater than that MAX ID. This will work as long as you're never doing delete operations on the tables.
Another way you could do it is to have a datecreated column in both tables, and then take the MAX datecreated from your destination table, and get all records from your source table which have a datecreated greater.
Yet another approach would be to have a column in your source table called "New", or something to that extent. This column would be a BIT field. Every time you insert a record into your source table, you would flag that record as NEW = 1. Then, in your scheduled task, you would take all the records who are flagged NEW = 1, copy them to your destination table, and set them to NEW = 0.
August 26, 2009 at 12:29 pm
The issue is, both of these products are canned applications and I can't change what's being posted to the database, just trying to expand their flexability in the backend.
August 26, 2009 at 12:41 pm
Fair enough, so no modification of the table structures, but you should still be able to write an SSMS job, no? Or, technically speaking, you could even just use a Windows scheduled task which executes a SSMS stored procedure, if you can't use SSMS jobs.
All you need to be able to do is identify which records are new. In order to do that, you can use any of the approaches I mentioned.
In fact, now that I think about it, if every 24 hours is fine, then why can't you just do a restore of the table through a scheduled task, or through another tool like Redgate?
August 26, 2009 at 12:50 pm
Can't really do a restore, the tables are completely different. Whenever a employe is created in the HR system, there are many things like employID, name, address, spouce, sex, etc.... that goes into that table; however, in the other system I'm wanting to send the data to, I just want the employID and Name.
August 26, 2009 at 12:56 pm
Alright - well, is EmployID an Identity field? And, if so, do both tables share identical records for that?
If so, you can use the MAX(EmployID) approach to find out which records are new.
If it isn't, then you've got a bit more difficult problem on your hands, but its still not a big issue.
What you can do is, run a query along the lines of
INSERT INTO destinationTable (EmployID, Name)
SELECT sourceTable.EmployID, sourceTable.Name
FROM sourceTable
LEFT JOIN destinationTable ON sourceTable.EmployID = destinationTable.EmployID
WHERE destinationTable.EmployID IS NULL
As long as your EmployID columns in both tables are indexed, this shouldn't be a very long query. What itt'l do is, compare the source table against the destination, and retrieve any records which exist in the source table and not in the destination table, then insert those records into the destination table.
August 26, 2009 at 1:03 pm
awesome, I will try that! So, would I put that in a ssis package and schedule it, or is there a way that I can put it in sql and schedule it to run in there? Sorry, I'm still learning!
August 26, 2009 at 1:34 pm
Well, I'm still not completely up to speed on SSIS, but from what my coworker told me, SSIS can't be used for scheduled tasks unless you have the Developer or Enterprise version.
So, instead, just make a stored procedure which runs that INSERT statement which I posted above, then make a Job in the SQL Server Agent Jobs interface, which will run daily at whatever time you choose, and which just executes the stored procedure you created.
August 26, 2009 at 1:36 pm
You are the man, thank you very much! I'll try this in the morning and let you know.
Thanks,
Jordon
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply