Dynamic variable in sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Dynamic variable in sql query

Author

Topic

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-27 : 22:37:12

Hi,

I'm using Execute SQL task to write an update query which uses two database names.

My query will be like

update DB1.dbo.table1
set col1 = DB2.dbo.table1.col1

which uses two database names hardcoded in the query. Is there a way to make this query dynamic where the database names can be modified outside the package using configuration.

Thanks in advance.

- Varalakshmi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-27 : 22:39:18

what you need is Dynamic SQL.

Take a look at this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 03:23:00

quote:
Originally posted by varalakshmi

Hi,

I'm using Execute SQL task to write an update query which uses two database names.

My query will be like

update DB1.dbo.table1
set col1 = DB2.dbo.table1.col1

which uses two database names hardcoded in the query. Is there a way to make this query dynamic where the database names can be modified outside the package using configuration.

Thanks in advance.

- Varalakshmi

you need to add three variables to package
two of them will hold the names of database names and third one will hold the query string.
let variables be @[user::sourcedb], @[user::destdb] and @[user::sqlstring]
then @[user::sourcedb], @[user::destdb] will be assigned values of dbs (db1,db2)
for @[user::sqlstring] set evaluateasexpression property as true and give below as expression

"update t1
set t1.col1 = t2.col1
FROM " + @[user::destdb] + ".dbo.table1 t1
JOIN " + @[user::sourcedb] + ".dbo.table1 t2
ON t2.col = t1.col"

then add the variables @[user::sourcedb], @[user::destdb] to configuration set inside package as in below link

http://www.sqlis.com/post/Easy-Package-Configuration.aspx

and then you can set any values through configuration for the database
also in above query col in on condition represents columns by which two tables are related. Also both the dbs should be in same server for above query to work.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources