Update many tables without hardcoding the table name

Question

Update many tables without hardcoding the table name

ireshasun

SSC Rookie

Points: 33
More actions
March 26, 2008 at 2:24 am

#204813

I want to update over 700 tables. I don't know how to write a SQL query to update tables without hardcoding the table name.
Can anyone help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

debrucer1 SSCertifiable Points: 5366 More actions · Answer 1

You can write a query against the catalog tables (tab, in this example) that builds an SQL script to do the updates. You have to parse the information you need to update into your query against the catalog, and spool the output to a file (usually ending with .sql), and then execute the .sql file you spooled.

I am not currently in a position to give you a good example and have oracle instances waiting for me; but, this is the technique I would take initially.

Good luck!

David

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

gints.plivna Ten Centuries Points: 1256 More actions · Answer 2

At least I didn't understood where (Oracle or MS SQL) you have to do these updates and what kind of updates... OK assuming this is almost Oracle forum 😉 you have to use old trick to "generate SQL using SQL" as already suggested.

There is only one view (actually synonym for view) you have to remember only one name - DICT! And from that you can find all other data dictionary views. Another option of course is to use Oracle docs and specifically Reference manual in http://tahiti.oracle.com

SQL> desc dict

Name Null? Type

----------------------------------------- -------- --------------

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

SQL> select * from dict where table_name like 'DICT%';

TABLE_NAME

------------------------------

COMMENTS

------------------------------------------------------------

DICTIONARY

Description of data dictionary tables and views

DICT_COLUMNS

Description of columns in data dictionary tables and views

DICT

Synonym for DICTIONARY

Gints Plivna

http://www.gplivna.eu

Gints Plivna
http://www.gplivna.eu

soren kongstad SSC Veteran Points: 235 More actions · Answer 3

If you are doing in pl/sql (its the Oracle forum) then you can use execute immediate

Do something like

for v_tab in namecursor
loop
execute immediate('update table '||v_tab.name||' set colum=''newvalue''');
end loop;

where namecursor is a cursor looping over the tablenames.

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 4

ireshasun (3/26/2008)
I want to update over 700 tables. I don't know how to write a SQL query to update tables without hardcoding the table name.
Can anyone help me.

Hi. In case you have not gotten what you need already you can use something like this:

SELECT IDENTITY(INT, 1, 1) AS tblid, NAME

INTO #ListOfTables

FROM sys.objects

WHERE TYPE = 'U'

SELECT * FROM #ListOfTables

DECLARE @ObjectID INT

DECLARE @ObjectName VARCHAR(255)

DECLARE @TotalObjects INT

DECLARE @CmdString VARCHAR(2000)

SELECT @TotalObjects = MAX(tblid) FROM #ListOfTables

SELECT @ObjectID = MIN(tblid) FROM #ListOfTables

WHILE @TotalObjects >= @ObjectID

BEGIN

SELECT @ObjectName = NAME FROM #ListOfTables WHERE tblid = @ObjectID

SELECT @ObjectID = @ObjectID + 1

SET @CmdString =

'UPDATE ' + @ObjectName +

' SET list of fields with values'

EXECUTE (@CmdString)

END

DROP TABLE #ListOfTables

It wil iterate through all the user-defined tables in the database and perform the update you specify. The specific code you use in the @CmdString variable will depend on what exactly you want to do. You can limit the list of tables with additional filters in the Where clause. Look up sys.objects in BOL for additional fields that can help with the filtering.

HTH

Don