Using parameter values ​​in an SQL statement

Question

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

I read that the correct way to do this is to use the 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>

Since multiple tables will be 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 to be used. Then they can be used in all script. However, I'm not sure how to use the values ​​of variables in actual SQL operations. I want to achieve 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 to use @ variables in this code to make it work correctly?

, , , , NVARCHAR (50) - ?

Answer 1

, , , , , , - , SQL, . № 2 : http://www.mssqltips.com/tip.asp?tip=1160

SQL. , .

Answer 2

, sp_ExecuteSQL

Answer 3

nvarchar (50) - sysname. SQL Server ( nvarchar (128)) .

Answer 4

http://msdn.microsoft.com/en-us/library/ms188001.aspx - sp_executesql , , sql . @SERVER_FROM INSERT INTO, sql, sp_executesql.

nvarchar (50) , , :)

Answer 5

select, , sp_executesql :

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

Answer 6

I like to create templates for dynamic SQL things like this: it’s much easier to support complex statements, and sometimes it’s easier to process nested quotes — and certainly easier when conditions need to be repeated in several places (for example, 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)