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
