I am trying to insert entire model in database using my .net application. I am using a user-defined table type.
This is my procedure and user-defined table; I am using SQL Server 2012.
CREATE TYPE [dbo].[TmpAccessRequest] AS TABLE
(
[RequestId] [int] NULL,
[RequesterID] [int] NULL,
[RequestType] [int] NULL,
[NextApprover] [int] NULL,
[RequestStatus] [varchar](100) NULL,
[Delegation] [int] NULL,
[CreatedOn] [date] NULL,
[CreatedBy] [varchar](100) NULL,
[Description] [varchar](max) NULL,
[IsSepecialRequest] [bit] NULL,
[DelegationDetailID] [int] NULL,
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[ModifiedOn] [date] NULL
)
GO
CREATE PROCEDURE [dbo].[proc_SaveAccessRequest]
(@TmpAR TmpAccessRequest READONLY,
@IsUAMSRequest BIT,
@RequestID INT OUTPUT)
AS
BEGIN
INSERT INTO tblRequests (RequesterID, RequestType, NextApprover, RequestStatus,
Delegation, CreatedOn, CreatedBy, Description,
IsSepecialRequest, DelegationDetailID, IsActive, IsDeleted, ModifiedOn)
SELECT
RequesterID, RequestType, NextApprover, RequestStatus,
Delegation, CreatedOn, CreatedBy, Description,
IsSepecialRequest, DelegationDetailID, IsActive, IsDeleted, ModifiedOn
FROM
@TmpAR
SET @RequestID = SCOPE_IDENTITY()
--SET @RequestID=IDENT_CURRENT('tblRequests')
SELECT @RequestID
END
I want to check if duplicate data should not insert at the same time. So how can I do that with user-defined table type ?
Please find the changes done to your script to avoid inserting duplicate record. So i considered two columns data should be unique to avoid duplication for user understanding purpose
CREATE PROCEDURE [dbo].[proc_SaveAccessRequest]
(
@TmpAR TmpAccessRequest READONLY,
@IsUAMSRequest bit,
@RequestID int OUTPUT
)
AS
BEGIN
Insert into tblRequests
(
RequesterID
,RequestType
,NextApprover
,RequestStatus
,Delegation
,CreatedOn
,CreatedBy
,[Description]
,IsSepecialRequest
,DelegationDetailID
,IsActive
,IsDeleted
,ModifiedOn
)
SELECT
RequesterID
,RequestType
,NextApprover
,RequestStatus
,Delegation
,CreatedOn
,CreatedBy
,Description
,IsSepecialRequest
,DelegationDetailID
,IsActive
,IsDeleted
,ModifiedOn
FROM @TmpAR
WHERE NOT EXISTS ( SELECT 1
FROM tblRequests i
INNER JOIN @TmpAR o
ON i.RequesterID = o.RequesterID
AND i.RequestType = o.RequestType
AND i.NextApprover = o.NextApprover)
SELECT @RequestID = SCOPE_IDENTITY()
SELECT @RequestID
END