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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.