CODEWITHSUNDEEP
databaseoracle-database
fawad
fawad

Reputation: 141

Adding hard code data in table in one go

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.

Upvotes: 1

Views: 545

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

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.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

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

enter image description here

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;

Upvotes: 3

Related Questions