New-InsertStatement

Hello,
I' m back after some time. Had to do a lot of Ms-SQL -> Oracle conversions and optimizations.

Now I have a clear vision, of what has to be wrapped by PowerShell.

Lets look at the SQL Insert-statement. It looks rather innocent.

What can go wrong?

A common way to execute Insert-Statement is the use of the DBMS's on board tools like SQL-Management-Studio (resp. Query-Analyzer) or SQL*Plus.

When your varchars grow to moderate sizes SQL*PLUS nags you with insane linesize restrictions.

Further it requires special treatment of the character '@' which it uses to include further scripts from the current script (usefull feature by the way).

Querry-Analyzer is more tolerant, even when you insert into Text columns.

But don't think you can back at your long data using simple select-statments. There is a limit of I think 8000 characters for the output of each single column. And when your data contain linefeeds neither text nor grid display will satisfy you.


The next problem is more subtile and become evident when using Oracle.

It occurs when you are using lots of inserts into the same table.
This time the problem is performance.

Oracle individually compiles each insert-statement and stores its text in a limited resource called the shared Pool. There replacing valuable information by one time used garbage.

(Oracle DBAs find it unthinkable that we SQL-Server user can be lucky in a world without bind-variables. I joined some funny meetings in the past.)

To be honest there are some mystic settings to help here (cusor sharing etc..).
And SQL gains by use of transactions.

SQL by itself waited a long time a enhance the syntax with inserts of multiple rows using one statement.
SQL-Server 2008 has the feature, but this version isn't yet at our customers.

The lack of a general standard led the individual sellers of RDBMS solutions invent their own incompatible tools.

ms-sql bcp: I remember a never ending story of inserting using the wrong code-page.

ms-sql bulc-insert. Your sql-server needs read permitions to your data. Often you are forced to beg an admin.

Oracle DataLoader. That is the Orale kind of the solution. I like it least.

Those restrictions belong to the tools, not to the RDBMS.

When you access your Database using a programming language these restrictions vanish.

And PowerShell is a programming language. It has even the advantage that it doesn't require a compile step.

Next advantage is the possibility to hide differences in syntax like the (in SQL-Server optional fill word into) and least but not last the differences for datetime (Date) literals.

(Thank a lot Mister Snover for your vision of the object oriented pipeline in PowerShell. That said about SQL and Dateformats).


That is not the end of the story. If you have a grown database system, you would presumably change the order of some columns in your table to make the content more human readable.

Or some columns nearly allways happen to have the same value, which is not the defined default value.

All those I hope to attac using PowerShell as generator for Insert functions wrapping the INSERT-Statement.

I'll start with the SQL-SERVER solution, but I have identity columns, which have to be mapped to Oracle sequences in some way on my todo list.