Select three columns and insert four

I want to perform a select query from a table containing three columns into a table of four columns and insert nulls into the last column, which is of type date, in one query.

    Requires Free Membership to View

    Login

There are two ways to do this. One is explicit, the other implicit. Let's start with explicit:

insert
  into targettable
     ( col1, col2, col3, tdatecol )
select cola, colb, colc, null
  from sourcetable

Here, the SELECT list contains an explicit NULL. Sometimes -- and I apologize for not being able to recall in which databases -- it is necessary to ensure datatype compatibility by explicitly casting the null:

insert
  into targettable
     ( col1, col2, col3, tdatecol )
select cola, colb, colc, cast(null as datetime)
  from sourcetable

Finally, since the target datetime column allows NULL, the implicit method is simply to omit the column from the query:

insert
  into targettable
     ( col1, col2, col3 )
select cola, colb, colc
  from sourcetable

In this implicit method, it may also be necessary that the target datetime column has DEFAULT NULL declared.

For More Information


This was first published in March 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top