I have created a stored procedure that accepts 7 parameters and inserts the values into a StagingTable (5 records) it then checks if the data in StagingTable exists in FinalTable. If it doesn't exists insert the values into the FinalTable. However, When I run the procedure all 5 records are inserted in the Staging table but only 1 record is inserted into the FinalTable. I'm new in writing code in sql so I'm not sure where is my mistake.
ALTER PROCEDURE [dbo].[sp_InsertBCCData_toStaging]
(-- Add the parameters for the stored procedure here
@JobNo varchar(50),
@Zip varchar(50),
@Country varchar(50),
@Copies varchar(50),
@AWB varchar(50),
@DelvCode varchar(50),
@OPSCode varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
------tranfer the BCC values in tblImportBCCDataStaging Table
INSERT INTO dbo.tblImportBCCDataStaging (JobNo, Zip, Country, Copies, AWB, DelvCode, OPSCode)
VALUES (@JobNo, @Zip, @Country,@Copies,@AWB,@DelvCode, @OPSCode);
------tranfer the Staging records in tblImportBCCData Table
INSERT INTO dbo.tblImportBCCData (JobNo, Zip, Country, Copies, AWB, DelvCode, OPSCode)
SELECT JobNo, Zip, Country, Copies, AWB, DelvCode, OPSCode
FROM dbo.tblImportBCCDataStaging t
WHERE NOT EXISTS(SELECT JobNo FROM dbo.tblImportBCCData WHERE JobNo=T.JobNo);
END
