Using Parameter Values In SQL Statement

Question

I am trying to write a database script (SQL Server 2008) which will copy information from database tables on one server to corresponding tables in another database on a different server.

I have read that the correct way to do this is to use a sql statement in a format similar to the following:

INSERT INTO <linked_server>.<database>.<owner>.<table_name> SELECT * FROM <linked_server>.<database>.<owner>.<table_name>

As there will be several tables being copied, I would like to declare variables at the top of the script to allow the user to specify the names of each server and database that are to be used. These could then be used throughout the script. However, I am not sure how to use the variable values in the actual SQL statements. What I want to achieve is something like the following:

DECLARE @SERVER_FROM AS NVARCHAR(50) = 'ServerFrom'
DECLARE @DATABASE_FROM AS NVARCHAR(50) = 'DatabaseTo'

DECLARE @SERVER_TO AS NVARCHAR(50) = 'ServerTo'
DECLARE @DATABASE_TO AS NVARCHAR(50) = 'DatabaseTo'

INSERT INTO @SERVER_TO.@DATABASE_TO.dbo.TableName SELECT * FROM @SERVER_FROM.@DATABASE_FROM.dbo.TableName
...

How should I use the @ variables in this code in order for it to work correctly?

Additionally, do you think my method above is correct for what I am trying to achieve and should I be using NVARCHAR(50) as my variable type or something else?

Thanks

Answer 1

have a look at http://msdn.microsoft.com/en-us/library/ms188001.aspx - sp_executesql takes a parameter that is a string and executes the sql in that string. so you'd need to concatenate @SERVER_FROM and other params with the INSERT INTO part to make the entire sql statement, and then pass to sp_executesql.

nvarchar(50) is fine, unless your server/database names are longer than that :)

Answer 2

You can create the select statement by concatenating all the information together and then use sp_executesql so:

sp_executesql 'INSERT INTO ' + @SERVER_TO + '.' + @DATABASE_TO + 
'.dbo.TableName SELECT * FROM ' +  @SERVER_FROM + '.' + 
@DATABASE_FROM+'.dbo.TableName'

Answer 3

There is probably a better way to do this, but what you are probably trying to do in your example is what's called dynamic SQL where you treat the statement as a string and the execute it. This would be section #2 here: http://www.mssqltips.com/tip.asp?tip=1160

There are some major downsides to dynamic SQL. You see a couple other approaches that might be better in that article.

Answer 4

If you want to execute a dynamically generated query then you have to use sp_ExecuteSQL

HTH

Answer 5

For the nvarchar(50) - you'd be better using sysname. This is a synonym in SQL Server (for nvarchar(128)) and represents the maximum length of an object identifier.

Answer 6

I like to make templates for dynamic SQL things like this - it's a lot easier to maintain complex statements and also sometimes easier to handle nested quotes - and definitely easier when terms need to be repeated in multiple places (like column lists):

DECLARE @sql AS nvarchar(max);
SET @sql = 'INSERT INTO {@SERVER_TO}.{@DATABASE_TO}.dbo.TableName
SELECT *
FROM {@SERVER_FROM}.{@DATABASE_FROM}.dbo.TableName'

SET @sql = REPLACE(@sql, '{@SERVER_TO}', QUOTENAME(@SERVER_TO))
SET @sql = REPLACE(@sql, '{@DATABASE_TO}', QUOTENAME(@DATABASE_TO))
SET @sql = REPLACE(@sql, '{@SERVER_FROM}', QUOTENAME(@SERVER_FROM))
SET @sql = REPLACE(@sql, '{@DATABASE_FROM}', QUOTENAME(@DATABASE_FROM))

EXEC(@sql)