Solved

Better Sql query for Inserting multiple records

Posted on 2008-06-26
23
1,308 Views
Last Modified: 2013-12-18
I'm currently using the SQL syntax shown below to add multiple new records into a database table,
however Im wondering if there is a better/simpler way to do it?

Any ideas and suggestions welcome.
Insert into buyers (org_id) select ('3125') from dual union all
select('3127') from dual union all
select('3128') from dual union all
select('3129') from dual;

Open in new window

0
Comment
Question by:tommyboyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
23 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21873041
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');
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21873170
the suggestion with the union alls is the most efficient one, for the database.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21873334
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.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:tommyboyd
ID: 21873476
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'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21873516
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  

Open in new window

0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21874148
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.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21875553
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!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21879981
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)




0
 

Author Comment

by:tommyboyd
ID: 21906632
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?

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 20 total points
ID: 21906687
Dr SQL's solution creates an array of the 4 values you are looking for then does a bulk insert (forall,  as opposed to a "for" loop)

I understand the <= 5 might not work, but does my second option, not work for you either?   It should be very quick, particularly for small numbers like these.


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

Expert Comment

by:DrSQL
ID: 21907223
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!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21908619
"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  :)
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21908726
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!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21908868
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?
0
 
LVL 3

Expert Comment

by:gajmp
ID: 21980529
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');
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21980681
gajmp insert all isn't a valid Oracle sql syntax.
0
 
LVL 3

Expert Comment

by:gajmp
ID: 21980714
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
0
 
LVL 3

Expert Comment

by:gajmp
ID: 21980727
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21983212
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?
0
 
LVL 74

Expert Comment

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

717 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