?
Solved

Oracle SQL: Insert select

Posted on 2007-10-17
5
Medium Priority
?
4,144 Views
Last Modified: 2010-08-05
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
Comment
Question by:JianJunShen
5 Comments
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 1000 total points
ID: 20092081
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
 

Author Comment

by:JianJunShen
ID: 20092103
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20092137
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
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 1000 total points
ID: 20092140
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
 
LVL 9

Assisted Solution

by:konektor
konektor earned 500 total points
ID: 20092143
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question