How to create portable inserts from SQL Server?

Question

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?

Answer 1

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.

Answer 2

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.