Openquery SQL how to check if exists?
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
精彩评论