Q

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close