Saving multiple data from different database to one database SQL and C#

Question

Here is the actual dummy program

Can I ask a question about this, how to save data from different databases into one database (MainDatabase). It is possible right?

I just want to have an idea making a simple program that can retrieve data from multiple databases with the SAME table name e.g transactionTBL, and then save it to one database with the same columns and value. btw I did tried using a stored procedure - but it have to be an object not varchar or string.

@DATABASE varchar(50)

INSERT INTO UserControlTenant.dbo.tenantData (tenant_name, receipt_id, amount, date, time)
    SELECT * 
    FROM ___.dbo.transactiondata

Example:

Database1

~transactiontbl~

ID
receiptID
amount
date time

Database2

~transactiontbl~

ID
receiptID
amount
date time

- MainDB

~transactiontbl~

ID
receiptID
amount
date time

It is as simple as providing a connection string per database. You can switch them at runtime as long as the schemas are the same. — Peter Bons
– Peter Bons, Commented Dec 22, 2018 at 11:02

ScaisEdge · Accepted Answer · 2018-12-22 11:06:15Z

1

if the databases are on the same server you could use an insert select based on union

INSERT INTO UserControlTenant.dbo.tenantData (tenant_name, receipt_id, amount, date, time)
select  'db1_tenant_name', receiptID, amount,date, time
from db1.dbo.tenantData 
UNION ALL 
select  'db2_tenant_name', receiptID, amount,date, time
from db2.dbo.tenantData 
UNION ALL
....
select  'dbn_tenant_name', receiptID, amount,date, time
from dbn.dbo.tenantData

use UNION ALL if you want all the rows values use UNION if you want only distinct result

answered Dec 22, 2018 at 11:06

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maves Newbie Here Over a year ago

I already did that.. it went well but my main problem is I want to pass the data drom db1,db2 to my MainDb.. UNION is not a problem but the problem is if I create a new database..

Maves Newbie Here Over a year ago

@DATABASENAME varchar(50) AS insert into UserControlTenant.dbo.tenantData (tenant_name, receipt_id , amount, date, time) select * from [@DATABASENAME].dbo.transactiondata

dawncode Over a year ago

you're trying to specify target db in the variable name; it won't work, you must need to specify target db the way scaisEdge specify

Subhash Gehlot · Accepted Answer · 2018-12-31 04:56:05Z

You could use the below cursor to fetch all databases from where you want to get the records and store in the other database

DECLARE @Database NVARCHAR(500) ;
DECLARE @Query NVARCHAR(1000);

DECLARE looper CURSOR FOR 
SELECT [name] FROM sys.databases 
WHERE [name] IN ('test','test1') --you could edit your where clause for the 
                                   database 
                                 --you need to fetch data from 
OPEN looper    

FETCH NEXT FROM looper     
INTO @Database   

WHILE @@FETCH_STATUS = 0    
BEGIN    


    SET @Query = '
    INSERT INTO UserControlTenant.dbo.tenantData (tenant_name, receipt_id, 
    amount, date, time)
    SELECT '+@Database +',receiptID, amount,date, time FROM 
    '+@Database+'.dbo.transactiondata  
    '

    EXEC (@Query)

FETCH NEXT FROM looper     
INTO @Database    

END     
CLOSE looper;    
DEALLOCATE looper;

Susindar · Accepted Answer · 2018-12-22 12:23:53Z

0

It can be achieved in many ways. The performance and duration need to be considered on all the scenarios. Best approach is do it on the SQL side itself using linked server "open insert" query . Another can be done using dynamic query with all the databases values merged together in stored procedure.

answered Dec 22, 2018 at 12:23

Susindar

75 bronze badges

Collectives™ on Stack Overflow

Saving multiple data from different database to one database SQL and C#

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related