Using Views for Insert Without Columns Names

[Former Member]

In this document I explain how to use views to insert values on base tables.

The problem

Let create the following table:

CREATE TABLE myFact  (

        myFID integer,

        myFDate     datetime,

        myFValue1 varchar(20),

        myFValue2 int,

        myFValue3 int )

GO

And let insert values in that table with a simple INSERT

INSERT INTO myFact

VALUES (1, getdate(), 'Fact Value 1', 100, 1000);

1 record(s) affected

[Executed: 15/08/2014 3:27:03 PM] [Execution: 500ms]

Now let assume that we need modify the myFValue1 column to be varchar(40).

ALTER TABLE myFact

ALTER myFValue1 varchar(40);

>[Error] Script lines: 14-17 ------------------------

SQL Anywhere Error -1013024: ALTER TABLE MODIFY <column> <datatype> is not supported.

-- (dblib/db_alter.cxx 411)  _

Since SAP IQ does not allows modifying the data type of a column, the only option available is to create a new column with the new data type, copy the data from the original column to the new column, drop the original column and rename the new column as the original one.

ALTER TABLE myFact

ADD myNewColumn varchar(40) NULL;

UPDATE myFact

SET myNewColumn = myFValue1

ALTER TABLE myFact

DROP myFValue1;

Since the columns are in different order, the insert without columns give an error.

INSERT INTO myFact

VALUES (2, getdate(), 'Fact Value 2', 200, 2000)

>[Error] Script lines: 45-49 ------------------------

SQL Anywhere Error -157: Cannot convert Fact Value 2 to a integer(10) (column myFValue2) _

[Executed: 18/08/2014 10:08:19 AM] [Execution: 0ms]

Even if inserting without columns names has been identified as a bad practice, we still find SQL code using it.

Imagine now that this is propagated in hundreds of store procedures that insert rows on hundreds of tables with hundreds of millions of rows each table.  Maintaining changes in the structure of the tables will demand a colossal amount of time and resources.

The Solution

1. Rename the base table.
2. Create a view derived from the base table (this view must have the same name as the original table).
3. Use the view for your insert.
4. Restructure and recompile the view every time the base table is modified.

Let drop and recreate the myFact table.

drop table myFact

GO

CREATE TABLE myFact  (

myFID   integer,

myFDate     datetime,

myFValue1 varchar(20),

myFValue2 int,

myFValue3 int

        )

GO

And, let insert a row in that table.

INSERT INTO myFact

VALUES (1, getdate(), 'Fact Value 1', 100, 1000)

Now, let modify the definition of the table as in the first part.

ALTER TABLE DBA.myFact

ADD myNewColumn varchar(40) NULL

GO

UPDATE myFact

SET myNewColumn = myFValue1

GO

ALTER TABLE myFact

DROP myFValue1

GO

ALTER TABLE myFact

RENAME myNewColumn TO myFValue1

GO

From or initial test, we know the insert without columns names won’t work. So let me test the proposed solution.

First, let rename the base table.

ALTER TABLE myFact

RENAME myFactBaseTable

GO

Now, let create a view derived from the base table.  This view will have the name of the base table before been renamed and the columns will follow the same order as the initial definition of the base table.

CREATE VIEW myFact

AS

SELECT myFID, myFDate, myFValue1, myFValue2, myFValue3

FROM myFactBaseTable

And now, we can insert on the view.

INSERT INTO myFact

VALUES (2, getdate(), 'Fact Value 2', 200, 2000)

1 record(s) affected

[Executed: 18/08/2014 10:35:41 AM] [Execution: 30ms]

If we need to modify the structure of the base table again, we need to recompile the view.

ALTER VIEW myFact RECOMPILE

GO

Please note the following limitation:

“Inserts can be done into views, provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.” [1]

[1] SAP IQ 16.0 SP08. Document Version: 1.0-2014-05-22 “Reference: Statement and Options”; SAP AG.

This post was first published in my personal blog at SAP IQ: Using Views for Insert Without Columns Names | ARBIME