Geek asks and answers

Conditional SQL targeting for different servers

How can I use a single piece of SQL conditionally modifying the target server?

So, on my local dev machine, I can run it and have a command ...

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [DEVSERVER].[cases].[dbo].[sp_class]
        WHERE   [status] = 1

But on a test machine, I want to run the same part of SQL and get the command ...

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [TESTSERVER].[cases].[dbo].[sp_class]
        WHERE   [status] = 1

I tried to use a variable @TGTand set it as devserveror testserverusing SQL FROM [@TGT].[cases].[dbo].[sp_class], but I get an errorCould not find server '@TGT' in sys.servers.

+3
source share
3 answers

You will need to use dynamic SQL. You should also not use 'single quotes'column aliases to distinguish between aliases; This syntax is deprecated. You should use [square brackets]or "double quotes"(the former is usually preferred).

DECLARE @sql NVARCHAR(MAX), @TGT SYSNAME;

SET @TGT = N'[TESTSERVER]';

SET @sql = N'INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS [Team number] ,
                [description] AS [Team name] ,
                [DutyEmail] AS [Team email address]
        FROM    ' + @TGT + '.[cases].[dbo].[sp_class]
        WHERE   [status] = 1;';

PRINT @sql;
EXEC sp_executesql @sql;

( , .)

, , , - . dev:

CREATE SYNONYM dbo.sp_class FOR DEVSERVER.cases.dbo.sp_class;

:

CREATE SYNONYM dbo.sp_class FOR TESTSERVER.cases.dbo.sp_class;

:

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class],
                [description],
                [DutyEmail]
        FROM    [dbo].[sp_class]
        WHERE   [status] = 1;

, , SQL Server 2005 . SQL Server, !

+6

. , , .

dev:

CREATE SYNONYM dbo.Cases_sp_class FOR [DEVSERVER].[cases].[dbo].[sp_class]

:

CREATE SYNONYM dbo.Cases_sp_class FOR [TESTSERVER].[cases].[dbo].[sp_class]

SQL :

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [dbo].[Cases_sp_class]
        WHERE   [status] = 1

:

  • TESTSERVER / DEVSERVER .
  • [sp_class] , . , , "sp_" , , MS , .
+2

, SQL , sql SQL

sql- if.

If <insert condition> then
INSERT  INTO [sds].[dbo].[Team_Email_Addresses]         SELECT  [class] AS 'Team number' ,                 [description] AS 'Team name' ,                 [DutyEmail] AS 'Team email address'         FROM    [DEVSERVER].[cases].[dbo].[sp_class]         WHERE   [status] = 1 
else
INSERT  INTO [sds].[dbo].[Team_Email_Addresses]         SELECT  [class] AS 'Team number' ,                 [description] AS 'Team name' ,                 [DutyEmail] AS 'Team email address'         FROM    [TESTSERVER].[cases].[dbo].[sp_class]         WHERE   [status] = 1

, , , - , , , , , . , , , , .

+1

More articles:


All Articles