January 18, 2011 at 8:44 am
i have a table like this
CREATE TABLE Tbl
(
[GUID] [uniqueidentifier] NOT NULL ROWGUIDCOL
[Address] [nvarchar] (255)
)
i need to write a sproc that will get as a parameter an address and will do the following:
if the address exists in the table it will return the existing address Guid
if the address does not exists, it will insert it and return the new Guid
i have implemented this using pre-Merge statements (select with lock and insert if not selected)
but was wondering if there is a way to do this using a merge statement
i got to this
MERGE Tbl AS [target]
USING (SELECT @ADDRESS) AS [source] ([Address])
ON ([target].[Address] = [source].[Address])
WHEN MATCHED THEN
UPDATE SET @GUID=[target].GUID
WHEN NOT MATCHED THEN
INSERT ([GUID],[Address])
VALUES (@GUID=newid(),@ADDRESS)
this should retrieve the Guid of the found record on the matched section but i have no idea how to do the same on the "Not Matched" section
any help would be appreciated
Dani Avni
OfficeCore
January 18, 2011 at 10:15 am
You appear to selecting not merging.
Try something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.GetTblGUID
@Address nvarchar(255)
,@GUID uniqueidentifier OUTPUT
AS
DECLARE @GUIDs TABLE
(
[GUID] uniqueidentifier NOT NULL
);
INSERT INTO Tbl
OUTPUT inserted.[GUID] INTO @GUIDs
SELECT NEWID(), @Address
WHERE NOT EXISTS
(
SELECT *
FROM Tbl WITH (UPDLOCK, SERIALIZABLE)
WHERE [Address] = @Address
);
IF @@ROWCOUNT = 0
SET @GUID = (SELECT [GUID] FROM Tbl WHERE [Address] = @Address);
ELSE
SET @GUID = (SELECT [GUID] FROM @GUIDs);
GO
-- eg of use
DECLARE @GUID uniqueidentifier
EXEC dbo.GetTblGUID 'Some Address', @GUID OUTPUT
SELECT @GUID
January 18, 2011 at 11:41 pm
Thanks for the example but i am looking to do the same using MERGE
not sure that it is possible but was wondering if it is possible somehow
Dani Avni
OfficeCore
January 19, 2011 at 5:15 am
Your update does not appear to be doing any updating so MERGE might not be the best way to do this.
Anyway you can add an OUTPUT clause to the merge to return the fields.
MERGE Tbl AS [target]
USING (SELECT @ADDRESS) AS [source] ([Address])
ON ([target].[Address] = [source].[Address])
WHEN MATCHED THEN
UPDATE SET @GUID = [target].GUID
WHEN NOT MATCHED THEN
INSERT ([GUID],[Address])
VALUES (newid(),@ADDRESS)
OUTPUT INSERTED.[Address], INSERTED.GUID;
January 19, 2011 at 10:17 pm
create procedure usp_Returnadd(@Address varchar(max))as
begin
insert into tbl
select newid(),@Address
WHERE not EXISTS
(
SELECT * FROM Tbl WHERE [Address] = @Address
);
select guid from tbl where address=@Address
end
--exec usp_Returnadd 'sample address'
January 19, 2011 at 11:57 pm
thanks for the posts. as i said i know how to do it without merge. i was looking for the merge way to do it
Dani Avni
OfficeCore
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply