开发者

Openquery SQL how to check if exists?

开发者 https://www.devze.com 2023-04-08 18:16 出处:网络
I am using sql OPENQUERY to check if record exist passing parameter update it, if not insert a new record.

I am using sql OPENQUERY to check if record exist passing parameter update it, if not insert a new record.

Here is what I have so far

DECLARE @sql VARCHAR (8000)
DECLARE @Id VARCHAR(20)

SET @Id= '31124'

SET @sql = '
SELECT开发者_StackOverflow * FROM OPENQUERY
(
   SERVERNAME, 
   ''
   SELECT Name FROM table WHERE Id= ''''' + @Id +  ''''' 
   ''
)'
EXEC (@sql)


DECLARE @sql VARCHAR (8000)
DECLARE @Id VARCHAR(20)

SELECT @Id= '31124'

SELECT @sql =
'
IF EXISTS
(
    SELECT * FROM OPENQUERY
    (
        SERVERNAME, 
        ''SELECT Name FROM table WHERE Id= ''''' + @Id +  '''''''
    )
) 
BEGIN  
    UPDATE OPENQUERY
    (
        SERVERNAME, 
        ''SELECT Name FROM table WHERE Id= ''''' + @Id +  '''''''
    )   
    SET Name = ''NewName''
ELSE
    INSERT OPENQUERY
    (
        SERVERNAME, 
        ''SELECT Name FROM table''
    )   
    VALUES
    (
        ''NewName''
    )
END 
'

EXEC (@sql)

Unless you have some specific requirement to use EXEC, you don't need it.

DECLARE @Id VARCHAR(20)

SELECT @Id= '31124'

IF EXISTS
(
    SELECT * FROM OPENQUERY
    (
        SERVERNAME, 
        'SELECT Name FROM table WHERE Id= ''' + @Id +  ''''
    )
) 
BEGIN  
    UPDATE OPENQUERY
    (
        SERVERNAME, 
        'SELECT Name FROM table WHERE Id= ''' + @Id +  ''''
    )   
    SET Name = 'NewName'
ELSE
    INSERT OPENQUERY
    (
        SERVERNAME, 
        'SELECT Name FROM table'
    )   
    VALUES
    (
        'NewName'
    )
END
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号