Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

SqlServer no dupes

I have an access solution that inserts the required rows into a results table. the PK is a concatenation of clientcode & batch & fieldname & data value.

With error trapping on access error 3022 I could ensure that no matter how many times the user ran the code it would never add a duplicate row.

with warnings off and error trapping this was invisible to the user and the form did its stuff.

Ive been tasked to move over to sql server.

I am using Visual studio to create ta windows application.

can I achieve the same? I have a usp_procedure but dont know how to handle the non dupes / reporting bits in vb.net code

my access code is here just for information, I have a working SP that needs a slight adjustment to create the concatenated PK. Not sure what happens when Sql Server gets send a dupe PK

"
    TableNamePrevious = "[tClient PREV]"
    ClientCodeName = "ClientCode"

    ClientCodeNameFull = "AbiCode"
    ModelID = "AbiCode"
    BatchName = "Batch"
    VehCatName = "VehCat"




Case "ADL"
TableName = ""
Case "CAP"
TableNameCurrent = "[CAPDATA]"
    TableNamePrevious = "[CAPDATA PREV]"
    ClientCodeName = "ClientCode"
    
    ClientCodeNameFull = "CAPid_CAPcat"
    ModelID = "CapVehicleID"
    BatchName = "BATCH"
    VehCatName = "CAP_cat"


Case "FEBI"
TableName = ""
Case "GLASS"
    TableNameCurrent = "[Glass Full Table]"
    TableNamePrevious = "[Glass Full Table PREV]"
    ClientCodeName = "ClientCode"
    
    ClientCodeNameFull = "GLASSid_GLASScat"
    ModelID = "Model_id"
    BatchName = "BATCH"
    VehCatName = "GLASS_cat"
Case "HALFORDS"
    TableName = ""
Case "HAYNES_PRO"
    TableName = ""
Case "KEE"
    TableName = ""
Case "KWIK_FIT"
    TableName = ""
Case "MAM"
    TableName = ""
Case "SMMT"
    TableName = "[SMMT]"
Case "TECDOC"
    TableName = ""
Case "TVI"
    TableName = "[TVIData]"
Case "TYRES"
    TableName = ""
Case Else
    MsgBox ("Shouldnt be here speak to Peter Bailey")

End Select

index = 0
TableFieldCount = db.TableDefs(TableNameCurrent).Fields.Count + 2

'number of slices for incrementing progress bar
lngSlices = Int(TableFieldCount)
Set ctl = Me.shpProgressBar

'Increment slice number
k = 0
i = 0

'Full control width is 5700 twips
lngSliceWidth = 5700 / lngSlices

'Set progress bar to starting position
ctl.Width = 0
Me.LblDerivation.Visible = False
Me.LblStartTime.Visible = False
Me.LblEndTime.Visible = False
Me.LblProcessTime.Visible = False

StartAt = Now
Me.LblStartTime.Caption = "Start time: " & StartAt
Me.LblStartTime.Visible = True
Me.LblDerivation.Visible = True
Me.LblDerivation.Caption = "Comparitor started"

MaxDateBuild = GetMaxBatchDate(ClientName, "CurrentBuild")

    For Each fld In db.TableDefs(TableNameCurrent).Fields
    '    MsgBox fld.Name
        
        If fld.Name = ClientCodeNameFull Or fld.Name = BatchName Or fld.Name = ModelID Then
        'ignore these fields
        
        Else
        'process these fields
   
        Select Case fld.Type
        Case 3 To 4
            Debug.Print fld.Name
            Debug.Print fld.Type
            tempstr = "INSERT INTO [TblCompareEvents](" & ClientCodeName & ", [Prev], [Change], ChangeYearMonth, VehicleCategory, Matched, ActualVariance, PKCodeChangeYearMonthField, FieldName)" _
            & " SELECT QryClientDifferencesLogic." & ClientCodeNameFull & ", " & TableNamePrevious & ".[" & fld.Name & "], " & TableNameCurrent & ".[" & fld.Name & "],'" & GetMaxBatchDate(ClientName, "CurrentBuild") & "' AS ChangeYearMonth, QryClientDifferencesLogic." & VehCatName & " AS VehicleCategory, IsMatched(QryClientDifferencesLogic." & ClientCodeNameFull & ",'" & ClientName & "') AS Matched, Round(Abs(Nz(" & TableNameCurrent & ".[" & fld.Name & "])-" & TableNamePrevious & ".[" & fld.Name & "])) AS ActualVariance, QryClientDifferencesLogic.[" & ClientCodeNameFull & "] & '" & GetMaxBatchDate(ClientName, "CurrentBuild") & "' & '" & fld.Name & "' & " & TableNamePrevious & ".[" & fld.Name & "] & " & TableNameCurrent & ".[" & fld.Name & "] AS PK, '" & fld.Name & "' AS FieldName" _
            & " FROM (QryClientDifferencesLogic LEFT JOIN " & TableNamePrevious & " ON QryClientDifferencesLogic." & ClientCodeNameFull & "= " & TableNamePrevious & "." & ClientCodeNameFull & ") LEFT JOIN " & TableNameCurrent & " " _
            & " ON QryClientDifferencesLogic." & ClientCodeNameFull & "=" & TableNameCurrent & "." & ClientCodeNameFull & " WHERE (((QryClientDifferencesLogic.[" & fld.Name & "Diff])=-1));"
            Debug.Print tempstr
            'Update the progress bar
            k = k + 1
            ctl.Width = lngSliceWidth * k
            Me.LblDerivation.Caption = fld.Name & " Events added"
            Forms!FrmGate.Repaint
        Case Else
            Debug.Print fld.Name
            Debug.Print fld.Type
            tempstr = "INSERT INTO [TblCompareEvents](" & ClientCodeName & ", [Prev], [Change], ChangeYearMonth, VehicleCategory, Matched, PKCodeChangeYearMonthField, FieldName)" _
            & " SELECT QryClientDifferencesLogic." & ClientCodeNameFull & ", " & TableNamePrevious & ".[" & fld.Name & "], " & TableNameCurrent & ".[" & fld.Name & "],'" & GetMaxBatchDate(ClientName, "CurrentBuild") & "' AS ChangeYearMonth, QryClientDifferencesLogic." & VehCatName & " AS VehicleCategory, IsMatched(QryClientDifferencesLogic." & ClientCodeNameFull & ",'" & ClientName & "') AS Matched, QryClientDifferencesLogic.[" & ClientCodeNameFull & "] & '" & GetMaxBatchDate(ClientName, "CurrentBuild") & "' & '" & fld.Name & "' & " & TableNamePrevious & ".[" & fld.Name & "] & " & TableNameCurrent & ".[" & fld.Name & "] AS PK, '" & fld.Name & "' AS FieldName" _
            & " FROM (QryClientDifferencesLogic LEFT JOIN " & TableNamePrevious & " ON QryClientDifferencesLogic." & ClientCodeNameFull & "= " & TableNamePrevious & "." & ClientCodeNameFull & ") LEFT JOIN " & TableNameCurrent & " " _
            & " ON QryClientDifferencesLogic." & ClientCodeNameFull & "=" & TableNameCurrent & "." & ClientCodeNameFull & " WHERE (((QryClientDifferencesLogic.[" & fld.Name & "Diff])=-1));"
            Debug.Print tempstr
            
'                                                                                                                                                                                                                                                                                                                                                                                                                                                                        [QryClientDifferencesLogic].[AbiCode] & '2017-01' & 'MAKE_DESCRIPTION' & [Tclient Prev] & [tClient].[Change] AS PK, 'MAKE_DESCRIPTION' AS FieldName
            'Update the progress bar
            k = k + 1
            ctl.Width = lngSliceWidth * k
            Me.LblDerivation.Caption = fld.Name & " Events added"
            Forms!FrmGate.Repaint
            
        End Select
    
            DoCmd.RunSQL tempstr

        End If
    index = index + 1
    Next
   'Update the progress bar
            k = k + 1
            ctl.Width = lngSliceWidth * k
            Me.LblDerivation.Caption = "Task added"
            Forms!FrmGate.Repaint

   x = SetEvent(4, ClientName)
       
   'Update the progress bar
            k = k + 1
            ctl.Width = lngSliceWidth * k
            Me.LblDerivation.Caption = "Current Compare batch Saved"
            Forms!FrmGate.Repaint

            
DoCmd.RunSQL "INSERT INTO TblBatchComparisons ( ClientCode, Change, Batch, Prev, ActualVariance, VehicleCategory, Matched, PKCodeChangeYearMonthField, ClientName )" _
& " SELECT QryAllChangesCurrentBatch.ClientCode, QryAllChangesCurrentBatch.Change, QryAllChangesCurrentBatch.MaxOfChangeYearMonth, QryAllChangesCurrentBatch.Prev, QryAllChangesCurrentBatch.ActualVariance, QryAllChangesCurrentBatch.VehicleCategory, QryAllChangesCurrentBatch.Matched, QryAllChangesCurrentBatch.PKCodeChangeYearMonthField,'" & ClientName & "' AS ClientName" _
& " FROM QryAllChangesCurrentBatch;"







MsgBox (ClientName & " Comparitor Task Set as complete")

EndAt = Now
Me.LblEndTime.Caption = "End time: " & EndAt
Me.LblProcessTime.Caption = "Processing time: " & Format(EndAt - StartAt, "hh:nn:ss")
Me.LblEndTime.Visible = True
Me.LblProcessTime.Visible = True
DoCmd.SetWarnings True



ControlExit_Exit:
    Exit Sub

ControlExit:
     If Err.Number = 3022 Then
        'no message
        Else
        MsgBox Error$
    End If
    Resume ControlExit_Exit



End Sub]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
correction...
Now I am reading your code to build test tables. I need to identify table name(s) and some of the column names to populate the tables by some sample data. You can help me doing this. In sql server environment, I can then use insert or update SQLs to make sure the constraints we are speaking about are doing their job.

If you already have the table(s) created, we should use ALTER TABLE... to install unique constraints like.
CONSTRAINT [UQ_columns] UNIQUE NONCLUSTERED
    (
        [column1], [column2], [column3]
    )

Open in new window

Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

so is constraint telling it in sql language that the columns are joined in a way to create a key. I imported the access db table to create the sql table, I hope that was right

here's a typical key in access at present:
109020C2016-12Introdate01/01/200501/01/2006   so here the clientcode was added to the batch date with the item that changedalong with the change values.

The column names for the table to insert into :
PKCodeChangeYearMonthField (nvarchar(255), not null) ClientCode (nvarchar(255), null)
Change (nvarchar(255), null)
ChangeYearMonth (nvarchar(255), null)
Prev (nvarchar(255), null)
ActualVariance (int, null)
VehicleCategory (nvarchar(255), null)
Matched (bit, not null)
FieldName (nvarchar(255), null)

User generated image
ok i think i get it i will create the alter and see how it goes.
ive done this but I have a syntax error near keyword constraint

use ClientData





ALTER TABLE TblCompareEvents
CONSTRAINT [UQ_columns] UNIQUE NONCLUSTERED
    (
        [ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
    )

Open in new window

maybe this is better
use ClientData


begin


ALTER TABLE TblCompareEvents
ADD CONSTRAINT [UQ_columns] UNIQUE NONCLUSTERED
    (
        [ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
    )

	end

Open in new window

not sure what UQ_Columns is
aha penny dropped
it gave a warning of:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'P_ID' has maximum length of 2550 bytes. For some combination of large values, the insert/update operation will fail.

I changed the PK Name too
Hi,
Can you please provide me the column names with their sizes of your table.
I did already on ID: 41994654
Hi Peter,
Your column sizes are very high.  For example - ChangeYearMonth , Maximum it is storing 7 characters and we have given nvarchar(255). Can we change the column sizes?  

If not possible or you want really quick fix then please use below-

--

IF NOT EXISTS ( SELECT TOP 1 1 FROM TblCompareEvents WHERE ClientCode = @ClientCode AND ChangeYearMonth = ChangeYearMonth 
					AND FieldName = @FieldName AND Change = @Change AND Prev = @Prev )
BEGIN
	   INSERT INTO TblCompareEvents ( [ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev] )
	   VALUES (@ClientCode, @ChangeYearMonth, @FieldName, @Change, @Prev)

	   SELECT 'Row Inserted..'
END
ELSE
BEGIN
		SELECT 'Row Already exist(s)'
END
--

Open in new window


Hope it helps!
yes they can be changed it was done in the wizard on import
Okies, Let me just change it for you.
Hi Peter,
Please try this -

--
CREATE TABLE TblCompareEvents
(
	 PKCodeChangeYearMonthField nvarchar(255)
	,ClientCode nvarchar(10)
	,Change nvarchar(50)
	,ChangeYearMonth nvarchar(8)
	,Prev nvarchar(50)
	,ActualVariance int
	,VehicleCategory nvarchar(255)
	,Matched bit not null
	,FieldName nvarchar(50)
)
GO

ALTER TABLE TblCompareEvents ADD CONSTRAINT [UQ_columns_1] UNIQUE NONCLUSTERED
(
    [ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
)
GO
--

Open in new window


Hope it helps!
ok I successfully changed the sizes:
PKCodeChangeYearMonthField (nvarchar(IOO), not null) 
ClientCode (nvarchar(25), null)
Change (nvarchar(IOO), null)
ChangeYearMonth (nvarchar(15), null)
Prev (nvarchar(IOO), null)
ActualVariance (int, null)
VehicleCategory (nvarchar(15), null)
Matched (bit, not null)
FieldName (nvarchar(50), null)

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We crossed over but it did it successfully now.
yes but i changed UQ to the PK name

use ClientData




ALTER TABLE TblCompareEvents
Add CONSTRAINT [PK_ID] UNIQUE NONCLUSTERED
    (
        [ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
    )

Open in new window

Great. So all done?
so i just need to do an insert but I will close this question off as its now answered.
Great. Also please let me know if you face issues while insertion.
ive shared the points more or less. thank you all.
Glad to help!