CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-agnosticportable-database
IAdapter
IAdapter

Reputation: 64747

How to create portable inserts from SQL Server?

Now it generates inserts like

INSERT [Bla] ([id], [description], [name], [version])
VALUES (CAST(1 AS Numeric(19, 0)), convert(t...

It's very SQL Server specific. I would like to create a script that everybody can use, database agnostic. I have very simple data types - varchars, numbers, dates, bits(boolean).

I think

insert into bla values (1, 'die', '2001-01-01 11:11:11')

should work in all DBMSs, right?

Upvotes: 3

Views: 334

Answers (3)

James Anderson
James Anderson

Reputation: 27478

There are a number of problems with number formats which will not port between dbmses however this pales when you look at the problems with dates and date formats. For instance the default DATE format used in an ORACLE DB depends on the whims of whoever installed the software, you can use date conversion functions to get ORACLE to accept the common date formats - but these functions are ORACLE specific.

Besides how do you know the table and column names will be the same on the target DB?

If you are serious about this, really need to port data between hydrogenous DBMSes, and know a bit of perl thn try using SqlFairy which is available from CPAN. The sheer size of this download should be enough to convince you how complex this problem can be.

Upvotes: 0

user330315
user330315

Reputation:

Some basic rules:

Get rid of the square brackets. In your case they are not needed - not even in SQL Server. (At the same time make sure you never use reserved words or special characters in column or table names).

If you do need to use special characters or reserved words (which is not something I would recommend), then use the standard double quotes (e.g. "GROUP").

But remember that names are case sensitive then: my_table is the same as MY_TABLE but "my_table" is different to "MY_TABLE" according to the standard. Again this might vary between DBMS and their configuration.

The CAST operator is standard and works on most DBMS (although not all support casting in all possible combinations).

convert() is SQL Server specific and should be replaced with an approriate CAST expression.

Try to specify values in the correct data type, never rely on implicit data conversion (so do not use '1' for a number). Although I don't think casting a 1 to a numeric() should be needed.

Usually I also recommend to use ANSI literals (e.g. DATE '2011-03-14') for DATE/TIMESTAMP literals, but SQL Server does not support that. So it won't help you very much.

Upvotes: 3

Jamiec
Jamiec

Reputation: 136114

A quick glance at the Wikipedia article on SQL, will tell you a bit about standardisation of SQL across different implementations, such as MS SQL, PostgreSQL, Oracle etc.

In short, there is a number of ANSI standards but there is varying support for it throught each product.

The general way to support multiple database servers from your software product is to accept there are differences, code for them at the database level, and make your application able to call the same database access code irrespective of database server.

Upvotes: 2

Related Questions