1

I am facing an issue when I am inserting data into table1 by selecting data from table2 like

INSERT INTO table1 SELECT * FROM table2

So there is a column in both table like field1 for which value is null in table2. But in table I have set the default value for field1 after executing ,

  INSERT INTO table1
     SELECT * FROM table2

I am having null value for field1 insted of getting default value of it what i set.

Jacob
  • 14,463
  • 65
  • 207
  • 320
Abby
  • 51
  • 2
  • 7

6 Answers6

1

You Can Specify the column names and then set the value in your Select Statement

INSERT INTO table1 (field1, field2, field3, ...)
SELECT field1, 
       CASE WHEN field2 IS NOT NULL THEN field2
            ELSE 'Your_default_value' END , 
       field3, ...
FROM table2
Hexxx
  • 413
  • 5
  • 13
  • For some rows it is null and for other rows there is value for field1 so i want for null value it should set default value . – Abby Apr 18 '17 at 06:23
  • I think your query will set all the records with default value for field1 – Abby Apr 18 '17 at 06:24
0
INSERT INTO table1 (field2, field3, ...)
SELECT field2, field3, ...
FROM table2

You just skip the column for which default value oyu want to use and it will be automatically populated with default.

nimdil
  • 1,361
  • 10
  • 20
  • As a side note you should avoid building inserts without explicitly listing columns as these will easily break once the table is restructured – nimdil Apr 18 '17 at 06:23
  • Thanks for your time and solution , why I need to mention all the column names simply why it is not inserting default value by executing "insert into table1 select * from table2. can you tell ? – Abby Apr 18 '17 at 08:25
  • Well, how is the database supposed to know what you're omitting? First column, last column, s.t. in the middle? The insert without column names works only if everything matches - if not you need to specify the mapping by listing the columns. – nimdil Apr 18 '17 at 12:09
0

Interesting, One way to achieve this is use of CASE EXPRESSION, Assuming that field2 is column which you wants to put default value

INSERT INTO TableName1 (field1, field2, field3, ...)
SELECT field1, CASE WHEN field2 IS NULL THEN 'DefaultValue' ELSE field2 END, field3, ...
FROM TableName2
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
  • Thanks Its working ,Really thankful to you but why is that simple query is not working if i have already set the default value at table level. – Abby Apr 18 '17 at 08:22
  • Since you are trying insert null value in respective column, when you are not inserting any thing in respective column that time only default value get inserted. – Jaydip Jadhav Apr 18 '17 at 08:24
0

Try using NVL

INSERT INTO TableName1 (field1, field2, field3, ...)

SELECT field1, NVL(field2,'Your Default Value'), field3, ... FROM TableName2

Learner
  • 45
  • 11
  • [IsNull does not seem to be available on Oracle](http://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function) – Imanuel Apr 18 '17 at 06:45
0

For example:

INSERT INTO DESTINATION_TABLE(A, B, C, D, E, F)
   SELECT W, X, Y, 'Some custom value', CASE WHEN Z IS NULL THEN 'Some other value' ELSE Z
FROM SOURCE_TABLE
WHERE ....
sameh.q
  • 1,691
  • 2
  • 23
  • 48
0

Any time you want to replace a null value, use COALESCE. COALESCE is available in both Oracle and SQL Server, it doesn't evaluate arguments unless needed (NVL always evaluates the second argument), and it is simpler than a case statement:

Insert into table1(field1, field2)
  select field1, coalesce(field2, field3, field4, 'novalue') from table2;

One big difference between NVL and COALESCE, if the second argument has side effects, you could come up with different results.

Brian Leach
  • 2,025
  • 1
  • 11
  • 14