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)
 Master DB Inserts/Updates to child DBs

Author  Topic 

dpnadam
Starting Member

29 Posts

Posted - 2011-02-16 : 11:44:18
Hi

Imagine 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 illustration
DECLARE @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.
   

- Advertisement -