Using a variable value in Automation SQL

Is it possible to use a variable value in SQL as part of an automation? I’ve seen examples where I can use a variable value in the WHERE clause to filter values selected. That’s not what I’m talking about. Here’s my use case:

I have many tables on one database and I want to copy the data from them to another database. I just want to select all the rows from table1 in database A and insert into table1 in database B. I’ve been able to write an automation to do it for one table, but I’d have to change it each time I run it for a different table name.

I have a list of all the tables and I want to loop through the list and replace the table name in the sql each time with the name from the current row in the list. I was thinking I could do something like this, but it doesn’t like the # signs in the SQL.

Select * from #tableName#

Any of you automation experts run into something like this before or have any ideas? Please ask for clarification if it doesn’t make sense.

Thanks in advance for any assistance

Hi,

I tried your scenario with Loop Dataset. I created my Excel (CSV) file with list of tables. I added this file as connection to TDP and use it in Loop Dataset. Then I just use select statement as you mentioned above and export data to Excel file table by table:

select * from Filip.#Loop_data_1_SQL#

Unfortunately then I found out that I am not able use variable in import wizard activity so I am not able import data to specific new table.

Second option which I am thinking about is using Compare Data activity. But it means that structure on database B have to prepare. I don’t know what provider are you using and how complicated would be get your DDL scripts for table but it should be option 2. (you can also thinking about running create script in loop and then run data compare activity).

If I get better idea I will let you know.

Filip

This can be done but it is not Toad that is your problem, it is SQL. SQL does not like variables as table names or fields, just values. You have to use dynamic SQL. Basically build the entire SQL command as string and then execute the SQL command string:

Declare @SqlCmdString as varchar(2000) = 'select * from ’ + #Loop_data_1_SQL#

Exec (@SqlCmdString)

Try that.

Note: for Oracle use || instead of + to concatonate text and Execute Immediate YourVariableName instead of Exec(YourVariablenName).

Can I ask why the server even sees that there is a variable? Shouldn’t TDP change the variable prior to being sent to the server? If not, why not?

Hello,

As far as I know the automation variables are replaced by the values in Toad before the SQL statement is executed. So the DB server doesn’t see the variables, only values.

Libor

Thanks for your reply Filip.

Thanks Greg. I’ll try your suggestion this week.

Peter and Libor,

Good question. Bind variables would be treated the same but why not convert #…# variables to values before running. Would eliminate the need for dynamic sql!

A long time ago Henrk added Toad Scripting. http://toad.henrik.org/2009/07/flow-control-for-your-scripts.html

One of the things this support is a variable for table name.

This is support if you use this special Toad Syntax. I have one user I know that uses this with Db2 in Automation

Create table {{Unquote(:name)}} as select * from dual