CODEWITHSUNDEEP
sqloracle-databaseinsertpopulatetest-data
Space Turtle 84
Space Turtle 84

Reputation: 3

What is a quick way to insert test data into tables in Oracle/Generate insert statements with different values (SQL)?

I'm trying to populate my tables with test data, but I'm looking for a way to do so without copying and pasting the same insert statement for each table repeatedly for ages and changing the values.

Is there a simple and fast way to create a bunch of a INSERT statements with different data for each column, perhaps getting data from a spreadsheet and inserting them into a insert statement?

Upvotes: 0

Views: 3271

Answers (3)

s0n1c
s0n1c

Reputation: 78

If you have access to a directory object and a spreadsheet, you could convert that spreadsheet into a CSV, and then load it as an external table. Once it's in an external table, you could do something like

INSERT INTO my_table ( column1, column2, column3 )
SELECT column1, column2, column3
FROM   <EXTERNAL-TABLE-NAME-GOES-HERE>
WHERE  test_id = 1;

If you're using Oracle 18 or newer, you can use the answer provided here: https://stackoverflow.com/a/49077724/1257557 which looks like this:

SELECT time_id, prod_id, quantity_sold, amount_sold
FROM   EXTERNAL (   
    (time_id        DATE NOT NULL,     
     prod_id        INTEGER NOT NULL,
     quantity_sold  NUMBER(10,2),
     amount_sold    NUMBER(10,2))     
    TYPE ORACLE_LOADER     
    DEFAULT DIRECTORY data_dir1 
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY '|')  -- You'll want to change this to a comma, if it's a CSV   
   LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;

It is worth noting that this solution requires you to have access to the file system on the database server so you can place the file(s) in whatever folders that the DB needs to read them from. If you don't have that access, then this option will not work.

If you're working with spreadsheets, you could consider creating columns or a macro that generates the insert statements for you once you've updated the spreadsheet, then you just copy/paste your statements from there to SQL Developer/SQLPlus/whatever.

Upvotes: 0

MT0
MT0

Reputation: 167774

Create 2 tables, one to contain the data you are going to using in your tests and the second is the one your queries are actually going to use:

CREATE TABLE test_data_sources (
  test_id NUMBER,
  column1 NUMBER,
  column2 DATE,
  column3 VARCHAR2(20)
);

CREATE TABLE my_table (
  column1 NUMBER,
  column2 DATE,
  column3 VARCHAR2(20)
);

Then, if you want to set the data for your first test:

DELETE FROM my_table;
-- or TRUNCATE my_table;

INSERT INTO my_table ( column1, column2, column3 )
SELECT column1, column2, column3
FROM   test_data_sources 
WHERE  test_id = 1;          -- replace with the id of whichever test you want to perform.

Then you can run your test against the MY_TABLE table with the appropriate data and then repeat and replace the data in the table with the data for the next test.

You need to populate TEST_DATA_SOURCES once (you can generate the DML statements from a spreadsheet if you want) with the appropriate data for each test but then it will be there to re-use each time you want to re-run the test.

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6084

You can create sample data very easily using the various functions in the DBMS_RANDOM package.

CREATE TABLE test_data
AS
        SELECT DBMS_RANDOM.VALUE (), DBMS_RANDOM.string ('x', 20)
          FROM DUAL
    CONNECT BY LEVEL <= 100;

Upvotes: 1

Related Questions