• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4150
  • Last Modified:

Oracle SQL: Insert select

Can someone understand following schema: Why two first two will fail? In the will succeed case, why one could select from one column zip_code?

===================================================
CREATE TABLE zip_new (
zip_code     VARCHAR2(5) NOT NULL,
state_abbrev VARCHAR2(2) NOT NULL,
city_name    VARCHAR2(30));

-- will fail
INSERT INTO zip_new
SELECT *
FROM zip_code;

-- will fail for a different reason
INSERT INTO zip_new
SELECT zip_code, state_abbrev, city_name
FROM zip_code;

-- will succeed
INSERT INTO zip_new
SELECT zip_code, state_abbrev, city_name
FROM zip_code
WHERE zip_code IS NOT NULL
AND state_abbrev IS NOT NULL;

SELECT * FROM zip_new;
0
JianJunShen
Asked:
JianJunShen
4 Solutions
 
GGuzdziolCommented:
1. Probably will fail because of different column types / column order in both tables.
2. Probably will fail because of the NOT NULL constraints in target table.
3. Will work because columns are explicitly specified and rows with NULLs either in zip_code or state_abbrev are being explicitly excluded.
0
 
JianJunShenAuthor Commented:
FROM zip_code

What this mean in successful case? Can we omit?

INSERT INTO zip_new
SELECT zip_code, state_abbrev, city_name
WHERE zip_code IS NOT NULL
AND state_abbrev IS NOT NULL;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
putting error messages would be helpful...

anyhow:
the first one fails primarily because the number of columns in zip_new and zip_code is different, or data types don't match.

the second can fail for the same reason, or, seing the the third one will succeed, because the data contains null values and the table zip_new does not allow null values on those columns.


the best syntax (in regards to 1.) would be to also include the destination column on the target table, like this:

INSERT INTO zip_new ( zip_code, state_abbrev, city_name )
SELECT zip_code, state_abbrev, city_name
FROM zip_code
WHERE zip_code IS NOT NULL
  AND state_abbrev IS NOT NULL;


0
 
GGuzdziolCommented:
No! This tells what is the source table, i.e. where do you take data from.

Please make sure that you are familiar with SQL syntax http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm


INSERT INTO zip_new -- put data into zip_new table
SELECT zip_code, state_abbrev, city_name -- take these three columns
FROM zip_code -- take records from zip_code table
WHERE zip_code IS NOT NULL -- exclude records with empty zip_code
AND state_abbrev IS NOT NULL; -- exclude records with empty state_abbrev
0
 
konektorCommented:
you've created zip_new with NOT NULL constraints on columns zip_code, state_abbrev ... so bulk insert from zip_code will fail due to there are some rows in it, which has null value in one or both columns zip_code, state_abbrev
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now