Common Import format to import data and database schema from various SQL dbs

Question

Common Import format to import data and database schema from various SQL dbs

itlk

Mr or Mrs. 500

Points: 578
More actions
April 12, 2022 at 5:47 pm

#4014691

Out customers use different SQL databases, like PostgreSQL, SQL Server, MySQL. We need to import data with a schema into our MS SQL Server database each time the customer sends us their DB dump.
The problem is, that different databases uses dump files in different formats, and that makes it difficult to automate the process.
Ideally we would receive a dump in a single common format, regardless of the customer's database. This could be eventually a CSV format. But which format should be used for the database schema, so that PostgreSQL or MySQL, etc., would create a dump file with the same structure/syntax?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

itlk wrote:

But which format should be used for the database schema, so that PostgreSQL or MySQL, etc., would create a dump file with the same structure/syntax?

There is no such thing. Each of the various RDMS's had implemented and extended the ANSI SQL standards differently. Unless the databases have been written specifically using only ANSI standard code (doubtful), exporting the schema's from one and attempting to execute it on another will require changes to be made. The other issue is the datatypes. They are different across platforms.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

So, you're best bet is start with a CSV file. Instead of trying to take MySQL/PostgreSQL/Oracle backups/dumps, export the data into a common format; CSV, JSON, whatever. Then, import from that common format. More work? Yes. However, as you've discovered, you can't restore backups from other database systems into SQL Server. You have to deal with data movement.

Azure Data Factory offers a whole bunch of possible solutions here. Older school, you could look to SSIS.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

I'll also tell you that a lot of companies aren't going to send you a database backup because there's a whole lot of stuff in a backup that they might not want you to see including but not limited to the names of users that have some form of priv associated with the database.

CSV is usually fine... true CSV is better, and JSON is fine but tag-bloat can get ya.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)