Capture the Error without having to do where not exists

1

---This fails, as there are two records in TABLEA and when insert into TABLE B
---Only one record should exist at the end of it into TABLEB.
---There should be a record in the TRACK table.

Results are nothing, i don't get anything in TABLEB or the TRACK table

CREATE TABLE [dbo].[TableA](
[Value1] varchar NOT NULL)

GO

CREATE TABLE [dbo].[Tableb](
[Value1] varchar NOT NULL)

CREATE TABLE [dbo].[Track](
[Value1] varchar NOT NULL
) ON [PRIMARY]
GO

--Two rows exists in TableA
Insert into TableA (Value1)
select '1'

Insert into TableA (Value1)
select '1'

---There should be 2 records created in Track and one record in TableB
---There is a duplicate row so it doesn't write anything to the Track Table

TRUNCATE TABLE Track
TRUNCATE TABLE TableB

-----Write all to TRACKER

DROP TABLE IF EXISTS #TEMP_TableB

BEGIN
BEGIN TRANSACTION
BEGIN TRY
SELECT * INTO #TEMP_TableB from TableA

				INSERT INTO TableB (Value1)
				select * FROM #TEMP_TableB
				


	INSERT INTO Track(value1)
	SELECT	*
		FROM #TEMP_TableB

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF XACT_STATE() <> 0

		INSERT INTO Track(Value1)
		SELECT Value1
		FROM #TEMP_TableB
		ROLLBACK TRANSACTION

END CATCH
END

SELECT * FROM Track
SELECT * FROM TableB

2

There are a few things wrong with this.
First, this inserts every record from TableA into Temp_TableB and then TableB.
INSERT INTO TableB (Value1)
select * FROM #TEMP_TableB

This will insert the first record. I partition by value1, but it should be whatever the PK.Unique key is, then order by whatever you want to order by, Are the rows identical? If so, you can do distinct, but usually the date/time stamp is different.
INSERT INTO TableB (Value1)
select Value1 from (
select Value1, row_number() over (partition by value1 order by value1) as rownum
FROM #TEMP_TableB) c
where RowNum = 1

Second, the insert in the catch block is getting rolled back, so it will never be seen.

Third, why are you putting everything from TableA into a temp table? You can avoid writing the data a second time by just referencing TableA.

3

i figured it out today, it was the rollback, i removed this and it worked.
It does a little more in the select and inserts based on a few conditons, i just didn't provide the entire code for this as this was just a little mock up.

Thank you.