SQL Data clone

Jayraj Chhaya

I have multiple Db with same table schema in all DB.
Now I want to globalization.

I create a new DB and create same table scema.Now,I wan't to insert all records from all databases and checked when data Insert if same name is exsist then Update else Insert.

For example,

DbName :- [Db1,Db2,Db3]
Table Name :- Tax
Column Name :- 'Name'
Records Count :- [5,2,3]

OUTPUT
DbName :- Global
Table :- Tax
Column Name :- 'Name'
Total Records :- 10

If Name is already exsist in Tax table in global DB then update else insert.

Note :- all DB is different but schema is same.

Suggest and give advice for above problem statement.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar2y ago
Accepted answer
Hello Jayraj,

You can achieve via Merge statement in SQL, Please refer this thread will help you,

https://www.red-gate.com/simple-talk/databases/sql-server/learn/manage-data-over-time-with-sql-server-merge-statement/
+2
Anandu G Nath2y ago
@Jayraj Chhaya

use bulk copy
+1
Rajeesh Menoth2y ago
Hi,

I have written an article on the same topic and you can refer :

https://www.c-sharpcorner.com/UploadFile/2a6dc5/how-to-copy-or-generate-sql-databse-content-to-multiple-one/
+2
Naveen Kumar2y ago
You can use the MERGE statement in T-SQL. The MERGE statement allows you to perform both INSERT and UPDATE operations based on a specified condition. You have to write a dynamic query since you have multiple DB's to iterate.

Below the Merge example for a single table, which need to be modified dynamically to iterate for each databases and tables under it.
```
USE Global
GO

MERGE INTO Tax AS target
USING (
    SELECT Name
    FROM Db1.dbo.Tax
    UNION ALL
    SELECT Name
    FROM Db2.dbo.Tax
    UNION ALL
    SELECT Name
    FROM Db3.dbo.Tax
) AS source
ON target.Name = source.Name
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name)
    VALUES (source.Name);
```
+2