I want my stored procedure to fill a 'reject' table for the line from my staging and which can not be injected in my target table (for example a line without description which is NOT NULL in my target).
I don't have an idea about the structure of the reject table.
I can edit if you need more info!
There is my table staging
CREATE TABLE [staging].[B]
(
[ID] [varchar](250) NULL,
[Address] [varchar](250) NULL,
[Approved] [bit] NULL,
[Description] [varchar](1000) NULL;
)
And this is my target table:
CREATE TABLE [dbo].[A]
(
[ID] [varchar](250) NOT NULL,
[Address] [varchar](250) NULL,
[Approved] [tinyint] NULL,
[Description] [varchar](1000) NOT NULL;
)
This is my stored procedure code:
ALTER PROCEDURE [dbo].[StoredProcedure]
(
@processType Varchar(4),
@stagingTable Varchar(64),
@destinationTable Varchar(64),
@errorMessage NVarchar(MAX) = null OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @isLocaltran BIT = 0
BEGIN TRY
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @isLocaltran = 1
END
DELETE
FROM staging.B
WHERE staging.B.[Description] IS NULL
OUTPUT
deleted.*
INTO [dbo].[RejectTable]
MERGE dbo.A AS TARGET
USING staging.B AS SOURCE ON (TARGET.ID = SOURCE.ID)
SELECT
TARGET.[Address],
TARGET.[Approved],
TARGET.[Description]
EXCEPT
SELECT
SOURCE.[Address],
SOURCE.[Approved],
SOURCE.[Description]
)
THEN
UPDATE
SET TARGET.[Address] = SOURCE.[Address],
TARGET.[Approved] = SOURCE.[Approved],
TARGET.[Description] = SOURCE.[Description]
WHEN NOT MATCHED
THEN
INSERT (,[ID], [Address], [Approved], [Description])
VALUES (...)
)
Thanks very much for @Dan Guzman's comment. It helped me so much.
DELETE FROM Staging.b OUTPUT deleted.* INTO RejectTable WHERE..