CODEWITHSUNDEEP
c#sql
Maves Newbie Here
Maves Newbie Here

Reputation: 9

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

Here is the actual dummy program

enter image description here

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

Upvotes: 0

Views: 1245

Answers (3)

Subhash Gehlot
Subhash Gehlot

Reputation: 11

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;

Upvotes: 1

Susindar
Susindar

Reputation: 7

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.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Related Questions