Better Sql query for Inserting multiple records

It seems strange that you would chose to insert specific values like this, but if this is a one-off a more obvious way would be to issue multiple insert statements:

insert into buyers (org_id) values ('3125');
insert into buyers (org_id) values ('3127');
insert into buyers (org_id) values ('3128');
insert into buyers (org_id) values ('3129');

the suggestion with the union alls is the most efficient one, for the database.

I agree with angelIII that using a single insert statement with a number of unioned selects is more effecient, however, from the point of view of inserting 4 rows into a table, effeciency isn't an issue and using separate inserts with a values clause is the more intuitive way of doing it.

Both methods achieve the same end result.

I'd like to be able to use the 'And' command some how meaning I can just cut and paste a long list of numbers to the end of the statement.. so along the lines of.

insert into Buyers (org_id) 'number1','number2','number3','number 4'

you cannot do that, unless you have mysql:
insert into Buyers (org_id) values('number1'),('number2'),('number3'),('number 4')

in mssql, you would need the below helper function, and your insert would be like this:

insert into Buyers (org_id)
select * from dbo.ParmsToList( 'number1,number2,number3,number 4')

create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Select allOpen in new window

Where are you getting your numbers from?

If they are supplied in a file, then you could create an external table and write a simple insert to select the data from the external table and insert into the buyers table.

For example.

Create a file called mybuyers.csv stored on the database server containing the following four lines of data:
3125
3127
3128
3129

Assuming the mybuyers.csv file is saved in the /tmp directory (on unix), you would create the following directory object in Oracle:

CREATE OR REPLACE DIRECTORY TMP_DIR AS '/tmp';

Note: You may require specific privileges to be created to use the create directory command depending on which Oracle account you use.

Next, create an external table definition, identifying the file and the directory it resides in:

CREATE TABLE MYEXTERNAL_TABLE
(
  ORG_ID  VARCHAR2(20 BYTE)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY TMP_DIR
     ACCESS PARAMETERS
       ( fields terminated by ','
   missing field values are null
   ( org_id)
  )
     LOCATION (TMP_DIR:'mybuyers.csv')
  )


Now you can access the data in the mybuyers.csv file as if it were stored in the database and insert it into the buyers table:

insert into buyers (org_id)
select org_id
from myexternal_table;

For future loads, simply save your file with the name mybuyers.csv into the /tmp directory on the server and then run the above insert statement.

tommyboyd,
    You can use pl/sql to accomplish most of what you want.  Just create a table and populate it then use a forall to bulk bind and bulk insert the data:

begin
   declare
           type numtable is table of number;

           Mynumber numtable := numtable(3125,3127,3128,3129);
begin
    forall this in Mynumber.first..Mynumber.last
         insert into buyers values (Mynumber(this));
end;
end;
/

If you did this in sqlplus, you could replace the string od number with a substituion variable (like &orgids) so you could easily run the macro, just enter a string of numbers, run the pl/sql again, etc.
Good luck!

how about ...

insert into buyers
(
SELECT n
  FROM (SELECT     LEVEL + 3124 n
              FROM DUAL
        CONNECT BY LEVEL <= 5)
 WHERE n NOT IN (3126)
)

the above would be if you have a short semi-contiguous list and it would be easier to specify the missing values.

If instead you really do want to specify an IN list of included values... then generate a big list and  pick those to keep
something like this...


SELECT n
  FROM (SELECT     LEVEL  n
              FROM DUAL
        CONNECT BY LEVEL <= 3130)
 WHERE n  IN (3125,3127,3128,3129)

The numbers are fairly random so you can't just do a <=5 statement.

DR SQL solution looks most like what I had in mind, I'm not sure I fully understand whats its doing tho..

I'm using pl/sql -  ive never started a statement with a Begin or Declare before

I don't understand the Mynumber table part either.. can someone show me how I would do this with my example?

tommyboyd,
   As ststuber said, the "mynumber" is an array.  I do that by creating my own data type - "numtable".  I then initialize the mynumber table to the value of the numbers I want to insert ("casting" them as a numtable datatype).  Pl/SQL tables know their "first" and "last" boundaries, so I use that for the "loop".  And FORALL, even though it looks like a loop, isn't really.  It takes the SQL statement and binds the entire array(within the bounds I specified with first and last) and submits it as one bulk operation.  If you were inserting two columns, you'd declare another table and list it in the insert.  You could even have a different data type like "type chartable is table of varchar2(2000);".  

   The "begin/end" is what's known as an anonymous pl/sql block.  When there's someting you need to accomplish in Oracle and it's a single unit of work that you only need to monitor for success/failure, you can handle it more natively by doing all of it in Oracle (pl/sql).  This code has the advantage of being very easy to generate (dynamically build a string that has the pl/sql) so you could have as many values in the array as would fit in a CLOB.  It is submitted just like a call to a stored procedure in a higher level language.  SQLPLUS sees it as SQL and begins a SQL buffer (that can only be exitted with a period, semicolon, or slash.

    The biggest advantage to this approach is the bulk operation.  Even ststuber's sequence generation won't do that, although it could run faster if you did this:

insert into buyers
select to_number(substr('3125/3127/3128/3129/', trunc(n/5)*5+1,4))
FROM (SELECT     LEVEL  n
              FROM DUAL
        CONNECT BY LEVEL <= 4)
/
Where "4" in the CONNECT BY specified the number of entries.  there's also a SQL function that parses out elements, but this is simple enough.  Can also be generated.  Also may or may not use an array method to insert.

Good luck!

"Even ststuber's sequence generation won't do thatinsert into buyers "

no, but the select was never intended to do an insert.
the "select" portion was supposed to be  an alternate to the select in my first "insert-select-from..."
which assumed contiguous list of integers.

The second select would be used if the numbers weren't contiguous, (as in this case) in which case you use it for the insert.  Sorry, I guess I should have been more explicit and provided the final example...

insert into buyers
(
SELECT n
  FROM (SELECT     LEVEL  n
              FROM DUAL
        CONNECT BY LEVEL <= 3130)
 WHERE n  IN (3125,3127,3128,3129)
)

there are other ways to make this faster too.
Picking a better lower bound than the implicit 1 will be the best.
The trick to doing this efficiently is picking some "known" aspect of the data and using it to your advantage.

In my example, I used a known upperbound,  adding a known lower bound will be even more efficient.
DrSQL assumed a known number of elements to restrict his auto-generation.

I don't like to use the substring method because it's too easy to exceed the 4000 char in sql if the list may be dynamic and I don't want to introduce numeric conversions.

Also note, a dynamic "in-list" also has an upper limit.  If you need it be limitless (or just large) then you'll have to put the data into a table in which case there's no point in using a generated sequence.
As you can simple do

insert into buyers (select n from your_table)


ps.  it's sDstuber,  not sTstuber  :)

Agree with all of sdstuber's points, although a string can be 32k nowadays and the pl/sql array doesn't have the same limit as an IN - so that makes it another alternative to a table.  I've often run this type of code in batches of a few thousand at a time.

I also apologize for being rude enough not to read sdstuber's id correctly.  Doesn't indicate the respect I have developed for sdstuber's contributions.

Good luck!

yes pl/sql can have 32K,  but sql itself can still only have 4000 characters.
Yes, agreed, using the collection types does make your list effectively limitless.

And no problem,  I'm not offended about the misspellins, I figured it was just a minor misreading.
:)

I am curious about the background on this question.
For 4 values,  there's really nothing any of these options have to contribute in a significant way over the others.

So, I assume there is something a little larger in scale behind this for your real-world problem.
What is that?  It may help indicate a better way to do this.

For instance, if you're reading the data out of a web service, tcp socket, dbms_pipe, or most other non-sql data source, then I'd definitely use the array-based code. To bulk up the list of values to be inserted and then do them all at once.

If the data is coming from a file, have you considered sql*loader or external tables?

Insert all
into buyers (org_id)
values ('3125')
into buyers (org_id)
values ('3127')
into buyers (org_id)
values ('3128')
into buyers (org_id)
values ('3129');

gajmp insert all isn't a valid Oracle sql syntax.

sorry i missed the select.. try this one

Insert all
into buyers (org_id)
values (a)
into buyers (org_id)
values (b)
into buyers (org_id)
values (c)
into buyers (org_id)
values (d)
select '3125' a, '3127' b, '3128' c, '3129' d from dual

we can do like this also

Insert all
into buyers (org_id)
values ('3125')
into buyers (org_id)
values ('3127')
into buyers (org_id)
values ('3128')
into buyers (org_id)
values ('3129')
select * from dual

Milleniumaire,

insert all

is valid syntax in 10g and up


However, I think the asker was looking for something a little more dynamic,  the insert all variants are effectively the same as what the asker posted originally with insert of unioned selects from dual.

tommyboyd, can you answer my previous question about the full nature of this question and its background?
It's unlikely you're trying to improve the efficiency of the insert insert of 4 constants.

What is the total volume of data expected?  Where is it coming from?

with no further input from tommyboyd to address the followup questions, I think
a split between Milleniumaire, angeliii, DrSQL and sdstuber is appropriate.