Save multiple data from different database to one database

Question

how 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

I think Database1 and Database2 should be Linked with MainDB to access! — Prashant Pimpale
– Prashant Pimpale, Commented Dec 22, 2018 at 9:22
Nope.. the project is intended to be dynamic, like calling a multiple counter and gets is total sales and put it in one database (MainDB).. something like that.. — Toshi T.
– Toshi T., Commented Dec 22, 2018 at 9:25
what do you mean by dynamic? You can link databases and write Procedure on Database side for merging data. And you can execute your procedure on c# — Derviş Kayımbaşıoğlu
– Derviş Kayımbaşıoğlu, Commented Dec 22, 2018 at 10:15
I already did that it went well but in other cases like . cant put a string to an object @DATABASENAME varchar(50) AS insert into UserControlTenant.dbo.tenantData (tenant_name, receipt_id , amount, date, time) select * from [@DATABASENAME].dbo.transactiondata — Toshi T.
– Toshi T., Commented Dec 22, 2018 at 11:26

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-22 10:21:23Z

2

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

answered Dec 22, 2018 at 10:21

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

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

1 Comment

Toshi T. Over a year ago

wooahh!! thats new for me... Thanks for helping a rookie here.. is that T-SQL? btw thanks

Collectives™ on Stack Overflow

Save multiple data from different database to one database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related