insert wont work in SQL

If the insert fails the all operation is rolled back so you can't expect to find new records.
You need to fix first the duplicate error.

You're inserting into what looks to be a primary key field so you may not be able to do that insert if it is an identity field. What error are you getting?

its odd as it was working but I will amend as once this is corrected its finished its work

its odd as it was working

Was working until you loaded the first records, right? After that it's more natural that you can load repeated records so the NOT EXISTS clause will skip those ones.

its failed but I think it must be close

error message:
Msg 2627, Level 14, State 1, Line 6
Violation of UNIQUE KEY constraint 'PK_ID'. Cannot insert duplicate key in object 'dbo.TblCompareEvents'. The duplicate key value is (18503658, 201509, MANUFACTURED_TO, 2016, <NULL>).
The statement has been terminated.

it produced this

INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID, ClientName )
SELECT [tClient].[abicode], [tClient].[BATCH], [tclient PREV].[MANUFACTURED_TO] AS PREV, [tClient].[MANUFACTURED_TO] AS CHANGE, 'MANUFACTURED_TO' AS FieldName, [tClient].[abi_cat],ISNULL(CAST([tClient].[abicode] AS VARCHAR(1000)),'')
   + ISNULL(CAST([tClient].[BATCH] AS VARCHAR(1000)),'')
   + ISNULL(CAST('MANUFACTURED_TO' AS VARCHAR(1000)),'')
   + ISNULL(CAST([tclient PREV].[MANUFACTURED_TO] AS VARCHAR(1000)),'')
   + ISNULL(CAST([tClient].[MANUFACTURED_TO] AS VARCHAR(1000)),'') AS PK,
   'abi' AS ClientName
FROM [tClient]
    INNER JOIN [tclient PREV] ON [tClient].[abicode] = [tclient PREV].[abicode]
WHERE RTrim(LTrim(IsNull([tClient].[MANUFACTURED_TO],''))) <> RTrim(LTrim(IsNull([tclient PREV].[MANUFACTURED_TO],''))) AND NOT EXISTS (SELECT 1 FROM TblCompareEvents WHERE  [ClientCode] =[tClient].[abicode] AND [ChangeYearMonth] = [tClient].[BATCH] AND [FieldName] = 'MANUFACTURED_TO' AND [Change] =[tClient].[MANUFACTURED_TO] AND [Prev] = [tclient PREV].[MANUFACTURED_TO])

Select allOpen in new window

the select on its own without insert produced:

abicode	BATCH	PREV	CHANGE	FieldName	abi_cat	PK	ClientName
80750110	200903	0	2014	MANUFACTURED_TO	B	80750110200903MANUFACTURED_TO02014	abi
80750110	200903	0	2014	MANUFACTURED_TO	B	80750110200903MANUFACTURED_TO02014	abi
80750113	200907	0	2014	MANUFACTURED_TO	B	80750113200907MANUFACTURED_TO02014	abi
80750113	200907	0	2014	MANUFACTURED_TO	B	80750113200907MANUFACTURED_TO02014	abi

Select allOpen in new window

I just ran it using a different client on data already in the table and it worked as expected so, I think the sql is ok from the usp.

it must be something about the data itself

EXEC [dbo].[usp_ClientDifferencesLogicInsert] 'MANUFACTURED_TO', 'tClient', 'abicode', 'abi_cat', 'abi', 'tclient PREV', 'BATCH' fail messages
EXEC [dbo].[usp_ClientDifferencesLogicInsert] 'Horse_power', 'GLASS FULL TABLE', 'GLASSid_GLASScat', 'GLASS_cat', 'glass', 'GLASS FULL TABLE PREV', 'BATCH'

Select allOpen in new window

works

Can you confirm the fields used for 'PK_ID'?

I was just looking in general and in my access version i dont get duplicates when looking for the values between the two tables. The clientcode itself only appears once in each current and previous tables.

I cut the query back to try to see why. if its producing duplicate rows then something is wrong.

my original access version is here, this version works no problems with nulls and no dupes, I am moving to sql express as access is slow.

SELECT TClient.abiCode, (Trim(Nz([TClient].[MANUFACTURED_TO]))<>Trim(Nz([TClient PREV].[MANUFACTURED_TO]))) AS MANUFACTURED_TOdiff, TClient.ABI_cat AS VehCat
FROM TClient INNER JOIN [tClient PREV] ON TClient.abiCode = [tClient PREV].abiCode
WHERE ((((Trim(Nz([TClient].[MANUFACTURED_TO]))<>Trim(Nz([TClient PREV].[MANUFACTURED_TO]))))<>False));

Select allOpen in new window

I cut back the creation of the PK from the 4 columns to see if the basic query produced dupes (I will get to your first question in a little bit) it still produces duplicates, it would be better if it didnt, the access version doesnt have grouping as it never produced dupes in the first place it was a one to one comparison.

it will be doing un necessary work in its current form.

SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].['+@BatchFieldName +'], [' +@TableNamePrev + '].['  +@FieldName + '] AS PREV, [' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName  +''' AS FieldName, [' + @TableName + '].[' +@VehCatName + ']'   + '
FROM ['+ @TableName+']'+'
    INNER JOIN ['+@TableNamePrev +'] ON ['+ @TableName +'].['+@PKName +'] = ['+@TableNamePrev + '].['+ @PKName+']' +'
WHERE RTrim(LTrim(IsNull(['+@TableName +'].['+@FieldName +'],''''))) <> RTrim(LTrim(IsNull(['+@TableNamePrev +'].['+ @FieldName +'],'''')))'

Select allOpen in new window

output:
abicode              BATCH      PREV      CHANGE      FieldName                   abi_cat
18503658      201509      NULL      2016           MANUFACTURED_TO      C
18503658      201509      NULL      2016           MANUFACTURED_TO      C
18503658      201509      NULL      2016           MANUFACTURED_TO      C
18503658      201509      NULL      2016           MANUFACTURED_TO      C


I do think the insert has an issue as you rightly say but i think the question of dupes is more fundamental for now as the vb.net app that runs the usp has to process many fields so dupes will make it slow as i experienced.

I am just checking the raw tables and it appears that sql wizard has created dupes so ive messed up the import somehow

i will correct that now

thats sorted out the dupes i will get the query back to its original state and maybe find the PK_id issue

its working!

thank you, it was definitely not the insert but the not exists addition  serves its purpose perfectly

I will flip over to vb.net app and keep my fingers crossed