我正在尝试使用SP将记录提交到一个表中.
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,
以下是最好的方法：
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,@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-合并==>不知道如何写这个.
你认为最快的方式是什么？
解决方法
我很确定你已经弄清楚了.但有人可能会发现它很有用.
ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]
@shareID int,@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,@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
