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
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
here's a typical key in access at present:
109020C2016-12Introdate01/
The column names for the table to insert into :
PKCodeChangeYearMonthField
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)
ASKER
ok i think i get it i will create the alter and see how it goes.
ASKER
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]
)
ASKER
maybe this is better
use ClientData
begin
ALTER TABLE TblCompareEvents
ADD CONSTRAINT [UQ_columns] UNIQUE NONCLUSTERED
(
[ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
)
end
ASKER
not sure what UQ_Columns is
ASKER
aha penny dropped
ASKER
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
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.
Can you please provide me the column names with their sizes of your table.
ASKER
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-
Hope it helps!
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
--
Hope it helps!
ASKER
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 -
Hope it helps!
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
--
Hope it helps!
ASKER
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We crossed over but it did it successfully now.
ASKER
yes but i changed UQ to the PK name
use ClientData
use ClientData
ALTER TABLE TblCompareEvents
Add CONSTRAINT [PK_ID] UNIQUE NONCLUSTERED
(
[ClientCode], [ChangeYearMonth], [FieldName], [Change], [Prev]
)
Great. So all done?
ASKER
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.
ASKER
ive shared the points more or less. thank you all.
Glad to help!
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.
Open in new window