Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use a variable to dynamically specify the table name Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 2:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:48 AM
Points: 107, Visits: 436
Hi folks,

This is my next challenge: I'm using an Execute SQL Task to run an update. The source and target tables are in the same server but in different databases. Therefore I'm using the fully qualify name for the target table and set the connection to the source table. Then I need to parameterized both, the source and target tables for deployment flexibility. I can use a package configuration and store the connection string to the source, but the connection to the target is in the SQL statement, that is [db_name].[owner].[tablename]. I know that the name of the tables and the columns cannot be a variable.
Some people recommend to use an expression to build the SQL statement, but this is not an option for me, because the script is about 100 lines long, and the lines are also very large. That´s make the whole package not easy to maintain and so on.

Could anyone recommend me another option?

Kind Regards


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1423941
Posted Tuesday, February 26, 2013 3:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
The only ways I can think of doing this are by generating & running dynamic SQL statements either through a stored proc (passing the table names as variables) or a task script (again passing the table names as variables)

Mack
Post #1423960
Posted Tuesday, February 26, 2013 3:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
Mackers (2/26/2013)
The only ways I can think of doing this are by generating & running dynamic SQL statements either through a stored proc (passing the table names as variables) or a task script (again passing the table names as variables)

Mack


Or write all of the update logic in a single stored proc which encapsulates the cross-database logic and just call that - nothing dynamic required.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1423969
Posted Tuesday, February 26, 2013 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Just out of interest, how would that work if you wanted to change the database name?

Mack
Post #1423970
Posted Tuesday, February 26, 2013 3:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
Mackers (2/26/2013)
Just out of interest, how would that work if you wanted to change the database name?

Mack


Which database name?

Obviously, the stored proc has to live in a known location and if both the source and target database are completely dynamic, it would have to go into a separate database completely, which is known. Then it could accept parameters - source & target database and table info - and generate and execute dynamic SQL to perform the update.

But it's difficult to judge whether that level of dynamism is required, without more info from the poster.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1423972
Posted Tuesday, February 26, 2013 4:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Good point Phil.

Maybe a script task would be a better option as it is database independent.

Mack
Post #1423988
Posted Tuesday, February 26, 2013 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:48 AM
Points: 107, Visits: 436
Hi all,

thanks Phil and Mack for your quick answer.

In my scenario both options (stored procedure and script taks) work fine.

I need to update an operational CRM (Dynamics - SQL DB) using data from an analytical CRM (SAS+SQL DB). Both databases are in the same server, so I just need to parameterized the target or the source. It is just to make the deployment more flexible but is not a strong requirement. As I said, both solutions will work.

Kind Regards,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1423991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse