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.
Author |
Topic |
dpnadam
Starting Member
29 Posts |
Posted - 2011-02-16 : 11:44:18
|
HiImagine I have this scenario. I have a master HR database - this contains a list of all employees. I also have a child database for each department. Each database is named after the department.When a new employee is created/updated in the master database I need to reflect the change in the appropriate child database. What is going to be the best way to achieve this functionality?I was planning on having a trigger on the master employee table. When theres a new insert or update, the trigger will fire. From here I am split again at what approach to take. I like the idea of a single stored procedure 'Insert_Employee' for example, that took in the department name as a parameter. The procedure would simply have an insert statement ( INSERT INTO [dbo].[Employee](...) VALUES(...) ). And execute a 'USE' statement along with the department name as a dynamic query within the procedure.Pseudo:DECLARE @DPName VARCHAR(30); SET @DPName = 'Sales'; --Hard coded here for illustrationDECLARE @Use VARCHAR(50); SET @Use = 'USE ' + @Name;EXEC(@Use); -- Then perform my insert statement.Is something like that achievable or should I be looking at an entire dynamic sql query for the whole insert?, i.e.:'INSERT INTO '+@DPName+'.[dbo].[Employee].....'Any advice is much appreciated.Thanks. |
|
|
|
|