Sql-server – Replicate data from multiple tables with same structure to a single destination table with additional columns

replicationsql server

I have a table with say the following structure. This table is in multiple databases.

Name varchar(50),
Age int

I want to replicate the data from this table from multiple databases to a single table. The target table has an extra column TabCode. I want to populate this TabCode with a value depending on from where the data is coming.

I do not have the option of adding any new column to the source tables.

Name varchar(50),
Age int,
TabCode int

Can this be done in Microsoft Sql Server?

Best Answer

Yes this is achievable in Microsoft SQL Server, and there's a couple different ways you can go about it.

SSIS Package: You can create an SSIS package that pulls the data from the different source databases and consolidates it all into one table on the target database with the additional column "TabCode" filled in accordingly.
Replication + View: You can setup replication publications between your source servers to subscriptions on your target server. Then you can use a view that unions all the data together into one view with the additional column "TabCode" filled out.

Some of the things to consider about SSIS packages is that they require some upfront implementation (you create them as a Visual Studio SSDT project type), and they require a job to trigger when they run (typically a SQL Agent job). So they aren't necessarily very real time in data synchronization (though they can be scheduled to ran as frequently as every second - but their actual runtime will depend on how you implement the SSIS package).

Some things to consider about Replication + Views is you can accomplish near real time data synchronization (with Transactional Replication) and they require less upfront implementation, but can be a little tricky getting setup first time around. (Overall they're not too bad to setup and work well when they work.)

Related Solutions

Sql-server – Select values from 2 tables with same properties

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'
  ' + QUOTENAME(c.name) 
  + ' = COALESCE(es.' + QUOTENAME(c.name) 
       + ', s.' + QUOTENAME(c.name) + '),'
FROM sys.columns AS c
INNER JOIN sys.columns AS c2
ON c.name = c2.name
AND c.[object_id] = OBJECT_ID('dbo.SETTINGS')
AND c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
AND c.name NOT IN (N'name', N'ID' /* , ... potentially others ... */);

SET @sql += N' s.name, s.ID';

SELECT @sql += N', 
  s.' + QUOTENAME(c.name)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.SETTINGS')
  AND name NOT IN (N'name', N'ID') 
  AND NOT EXISTS 
  (
    SELECT 1 FROM sys.columns AS c2
      WHERE c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
      AND c2.name = c.name
  );

SET @sql += N'
  FROM dbo.SETTINGS AS s
  INNER JOIN dbo.EXTENDED_SETTINGS AS es
  ON s.ID = es.SETTINGS_ID
  WHERE es.ID = @extSetId AND s.ID = @settID;';

PRINT @sql;
--EXEC sp_executesql @sql;

Please use table aliases and schema prefixes, and here is why I prefer COALESCE over ISNULL. And if those tables really aren't stored as upper case in a case sensitive collation, do you really need all caps? Also, ID is a horrible and ambiguous name for a column. It should be the same name across the schema; always describing exactly what it is - especially when you're doing lazy dynamic things like this.

Mysql – How to auto replicate specific columns only from a table in MySQL

Why not create one separate db for your SSO, which is accessible for each site? Copying data around will create problems when two sites are editing "the same field".

If you only want to use a copy to set up, you could:

CREATE TABLE newdb.users LIKE otherdb.users;
INSERT newdb.users SELECT * FROM otherdb.users;

If you really want to continuously synchronise tables between databases, you will have to do it with a custom script. Most important to consider in that case, will be the question of what to do when conflicts arise? But I would really consider creating one specific db for SSO.

Best Answer

Related Solutions

Sql-server – Select values from 2 tables with same properties

Mysql – How to auto replicate specific columns only from a table in MySQL

Related Question