Adding hard code data in table in one go

Question

I came across the scenario where I have to add hard-coded data into table. There are thousands of entries, is there any way to add it through query in one go? The data is like in below mentioned format:

'1777193992', '1777535342', '1777519577', '1777725624', '1777311315', '1771416476', '1779312636', '1777125359'

I have tried this:

SELECT '1777193992', '1777535342', '1777519577', '1777725624', '1777311315', '1771416476', '1779312636', '1777125359' FROM dual;

But it's giving me data in row. I want the data to be inserted in column.

Where are the thousands of values coming from? If you have a file of them then an external table or SQL\*Loader would be options. If not there are other methods but you'd have to manipulate the list of values a bit. (Also what is the target column data type? All your examples are numbers but you are treating them as strings?) — Alex Poole, Nov 11 '16 at 08:10

score 3 · Accepted Answer · answered Nov 11 '16 at 08:32

Create a script with bunch of INSERT commands, one INSERT for each value.
Then load this script into your favorite editor, and run it in one go.

Ma favorite method in such a case is using spreadsheet to generate SQL commands.
With the spreadsheet I can generate script for hundreds of thousands of values in a couple of minutes.

A simple example (using Google sheets):

Values are in A column
In B1 cell enter this formula: ="INSERT INTO tablename( columnname ) VALUES( '"&A1&"' );"
Copy this formua from B1 cell to remaining cells in B column
Select column B and copy it's contents into a text editor and append COMMIT; at the end- this is our SQL script, ready to run "in one go" - just load it into SQL Developer and hit F5

INSERT INTO tablename( columnname ) VALUES( '1777193992' );
INSERT INTO tablename( columnname ) VALUES( '1777535342' );
INSERT INTO tablename( columnname ) VALUES( '1777519577' );
INSERT INTO tablename( columnname ) VALUES( '1777725624' );
INSERT INTO tablename( columnname ) VALUES( '1777311315' );
INSERT INTO tablename( columnname ) VALUES( '1771416476' );
INSERT INTO tablename( columnname ) VALUES( '1779312636' );
INSERT INTO tablename( columnname ) VALUES( '1777125359' );
COMMIT;

score 0 · Answer 2 · edited May 23 '17 at 11:53

If you don't want to generate and run lots of individual insert statements there are a few ways to combine the data as a single insert, such as selecting each individual value from dual and unioning them together:

insert into tablename(columnname)
select '1777193992' from dual
union all select '1777535342' from dual
union all select '1777519577' from dual
union all select '1777725624' from dual
union all select '1777311315' from dual
union all select '1771416476' from dual
union all select '1779312636' from dual
union all select '1777125359' from dual
/

or with the insert all syntax which slightly abuses the multi-table insert idea:

insert all
  into tablename(columnname) values ('1777193992')
  into tablename(columnname) values ('1777535342')
  into tablename(columnname) values ('1777519577')
  into tablename(columnname) values ('1777725624')
  into tablename(columnname) values ('1777311315')
  into tablename(columnname) values ('1771416476')
  into tablename(columnname) values ('1779312636')
  into tablename(columnname) values ('1777125359')
select * from dual
/

You could generate those from Excel by modifying what @krokodilko showed.

You can also create a collection that you populate with the individual values; here using the built-in odcivarchar2list collection type:

insert into tablename(columnname)
select column_value
from table(sys.odcivarchar2list('1777193992', '1777535342', '1777519577',
  '1777725624', '1777311315', '1771416476', '1779312636', '1777125359'))
/

... though you may have problems with more than 1000 entries listed like that, which you could work around by populating the collection from PL/SQL - which makes this approach less appealing in this case.

But if you have a list of values then you may find it much easier to load them through an external table or SQL*Loader, or even through SQL Developer's import mechanism.

If you have the list in your own application you might be able to populate a collection from an array and then use the table() version above, but exactly how would depend on the language and driver you are using.

Adding hard code data in table in one go

2 Answers2