Solved

Better Sql query for Inserting multiple records

Posted on 2008-06-26
23
1,293 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
  • 6
  • 4
  • 3
  • +3
23 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
Comment Utility
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 142

Expert Comment

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

Expert Comment

by:Milleniumaire
Comment Utility
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
 

Author Comment

by:tommyboyd
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 20 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
"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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
gajmp insert all isn't a valid Oracle sql syntax.
0
 
LVL 3

Expert Comment

by:gajmp
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now