Is it possible to use a variable as a table name in SQL?

Question

I am working on a legacy application using an Oracle 11g database and have come across an issue where I have 5 tables all in different schemas with the same data structure. I need to be able to insert data into these tables and I'm trying to avoid duplicate code.

I know do something like the following

IF p_schema = SCHEMA1 THEN 
  INSERT INTO SCHEMA1.TABLE_NAME_SCHEMA1 (...) VALUES (...)
ELSIF p_schema = SCHEMA2 THEN 
  INSERT INTO SCHEMA2.TABLE_NAME_SCHEMA2 (...) VALUES (...)
ELSIF p_schema = SCHEMA3 THEN 
  INSERT INTO SCHEMA3.TABLE_NAME_SCHEMA3 (...) VALUES (...)
ELSIF p_schema = SCHEMA4 THEN 
  INSERT INTO SCHEMA4.TABLE_NAME_SCHEMA4 (...) VALUES (...)
ELSIF p_schema = SCHEMA5 THEN 
  INSERT INTO SCHEMA5.TABLE_NAME_SCHEMA5 (...) VALUES (...)
END IF;

Is there any way to make the table name a variable inside the SQL so that I can just make one insert statement, but insert into the table I want?

Please describe your efforts and results so far. – Dan Bracuk Feb 06 '15 at 19:39 — Dan Bracuk, Feb 06 '15 at 19:39

score 3 · Answer 1 · edited Feb 06 '15 at 19:51

Can you? With dynamic SQL, sure. Should you? With just 5 tables, I'm not sure that the extra complexity of dynamic SQL is worth it.

p_sql_stmt := 'INSERT INTO ' || p_schema || '.TABLE_NAME (<<columns>>) ' ||
              '  VALUES( ?, ?, ?, ?, ?, ... ? ) ';
EXECUTE p_sql_stmt
  USING val1, val2, val3, val4, ... , valN;

If you are doing this sort of thing all commonly (rather than looking at one piece of code that has this ugly IF), assuming that you have a basic OLTP application, so that you're doing lots of single-row inserts where the overhead of triggers isn't going to be terrible, I'd probably consider creating a view that does a UNION ALL of the 5 tables along with an identifier that tells you which table the data comes from and an INSTEAD OF trigger that directs the INSERT to the proper table. Then all your other code could just reference the one view rather than having the IF statement all over the place. Something like

CREATE VIEW composite_view
AS
SELECT 'Schema1' schema_name, a.*
  FROM schema1.table_name a
UNION ALL
SELECT 'Schema2' schema_name, b.*
  FROM schema2.table_name b
...

And in the INSTEAD OF INSERT trigger

IF( :new.schema_name = 'Schema1' ) 
THEN 
  INSERT INTO schema1.table_name( <<columns>> ) 
    VALUES( :new.col1, :new.col2, ... );
ELSIF( :new.schema_name = 'Schema2' ) 
THEN 
  INSERT INTO schema2.table_name( <<columns>> ) 
    VALUES( :new.col1, :new.col2, ... );
...

score 2 · Answer 2 · answered Feb 06 '15 at 19:45

I think you can use insert all or insert first for this. Something like:

INSERT FIRST
    WHEN p_schema = 'SCHEMA1' THEN INTO SCHEMA1.TABLE_NAME_SCHEMA1( . . . )
    WHEN p_schema = 'SCHEMA2' THEN INTO SCHEMA2.TABLE_NAME_SCHEMA2( . . . )
    . . .
     . . .;

score 0 · Answer 3 · answered Feb 06 '15 at 19:44

you can use dynamic sql:

declare 
   schema_name varchar2(50);
begin
   v_name := 'SCHEMA1'; -- set the value of schema, you can do it in a loop also
   execute immediate
      'INSERT INTO '||schema_name ||'.TABLE_NAME_SCHEMA1 (...) VALUES (...)';
   commit; 
end;

score 0 · Answer 4 · edited May 23 '17 at 11:57

I think the best way to deal with this problem is to use procedure, which usage shown here

CREATE OR REPLACE TYPE employeeType AS OBJECT (employeeId INT, employeeName VARCHAR2(50));
-- this is how you can use variables
CREATE TYPE ttEmployeeType AS TABLE OF employeeType;

CREATE PROCEDURE testCustom (pLocation ttEmployeeType)
AS
BEGIN
        INSERT
        INTO    employee (emp_id, emp_name)
        SELECT  *
        FROM    TABLE(pLocation);
END;

Is it possible to use a variable as a table name in SQL?

4 Answers4