CODEWITHSUNDEEP
c#sql
Toshi T.
Toshi T.

Reputation: 35

Save multiple data from different database to one database

here is the dummy programhow can I save data from different databases into one database (MainDatabase)?

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: 423

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30665

In your case, you need database link in order to be able to access one database from another database.

INSERT INTO UserControlTenant.dbo.tenantData (tenant_name, receipt_id, amount, date, time)
    SELECT * from OPENQUERY(MyLinkedServer,'SELECT * FROM xxx.dbo.transactiondata');

or if it is mssql DB

INSERT INTO UserControlTenant.dbo.tenantData (tenant_name, receipt_id, amount, date, time)
    SELECT * from [SRVR002\ACCTG].dbo.transactiondata');

for creating database link check this reference

Upvotes: 2

Related Questions