Fast upsert Sql server 2008 R2
19,103
Solution 1
I'm pretty sure you've already figured this out. But someone might find it useful.
ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]
@shareID int,
@ownerId varchar(200),
@sharePermissions varchar(65)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
MERGE [dbo].SHARE_AD_GROUP AS T
USING
(
SELECT @shareID,@ownerId,@sharePermissions
) AS S
(
SELECT Share_ID,AD_Group,Share_Permissions
)
ON (T.SHARE_ID = S.Share_ID and T.AD_GROUP = S.AD_Group)
WHEN MATCHED THEN
UPDATE SET SHARE_PERMISSIONS = S.Share_Permissions
WHEN NOT MATCHED THEN
INSERT
(SHARE_ID,AD_GROUP,SHARE_PERMISSIONS)
VALUES
(S.Share_ID,S.AD_Group,S.Share_Permissions)
;
END
Solution 2
I think merge statement is faster then what you demonstrate in your 2 ways. if you need to know more about how to write MERGE INTO Query in sql server then please follow the links..
https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
and your merge block looks like this, you need to put that in your store procedure or any other places.
MERGE INTO SHARE_AD_GROUP A
USING (
SELECT SHARE_AD_GROUP_ID,
SHARE_ID,
AD_GROUP,
SHARE_PERMISSIONS
FROM SHARE_AD_GROUP
WHERE SHARE_ID = @shareID AND AD_GROUP = @ownerId
) B ON (A.SHARE_AD_GROUP_ID = B.SHARE_AD_GROUP_ID)
WHEN MATCHED THEN
UPDATE SET A.SHARE_PERMISSIONS = B.SHARE_PERMISSIONS
WHEN NOT MATCHED THEN
INSERT (SHARE_PERMISSIONS) VALUES(@sharePermissions);
![Maro](https://i.stack.imgur.com/QcruQ.jpg?s=256&g=1)
Author by
Maro
Updated on June 01, 2022Comments
-
Maro about 2 years
I'm trying to upsert records using SP into one table.
CREATE TABLE [dbo].[SHARE_AD_GROUP]( [SHARE_AD_GROUP_ID] [int] IDENTITY(1,1) NOT NULL, [SHARE_ID] [int] NOT NULL, [AD_GROUP] [varchar](200) NOT NULL, [SHARE_PERMISSIONS] [varchar](65) NULL,
what is the best way of the following:
1-
ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP] @shareID int, @ownerId varchar(200), @sharePermissions varchar(65) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE [dbo].SHARE_AD_GROUP SET [SHARE_PERMISSIONS] = @sharePermissions WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId if @@ROWCOUNT =0 begin INSERT INTO [dbo].SHARE_AD_GROUP (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS]) VALUES (@shareID,@ownerId,@sharePermissions) end end
2-
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. declare @id int SET NOCOUNT ON; set @id = (select top 1 SHARE_AD_GROUP_ID from SHARE_AD_GROUP where SHARE_ID = @shareID and [AD_GROUP] = @ownerId) if @id is null begin INSERT INTO [dbo].SHARE_AD_GROUP (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS]) VALUES (@shareID,@ownerId,@sharePermissions) set @id = SCOPE_IDENTITY() end else begin UPDATE [dbo].SHARE_AD_GROUP SET [SHARE_PERMISSIONS] = @sharePermissions WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId end End
3- Merge ==> No idea how to write that.
What is the fastest way in your opinion?
-
Maro almost 11 yearsAll examples uses 2 tables target and source, but in my case i'm passing SP parameters. i searched but couldn't find similar case.
-
Tejas Vaishnav almost 11 yearsI have updated my answer can you look at out and make appropriate changes according to your requirement.