Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: