How do I construct an insert query that respects case sensitivity?

Question

0.00/5 (No votes)

See more:

I have an SQL Server database where I regularly delete and replace records based on a particular key field. Every record in every table has this key field. The key field is case sensitive (Collation = Latin1_General_CS_AS).

Example: I have records in the database with the key field value being AAA. Depending on the table there may be one or many such records.

I want to upload new records with a key field value of aaa.

Step 1 is always to delete any existing aaa records. This is because the number of records being uploaded may be more, less or the same as before. This deletion works in the sense that the case sensitivity is respected and records with the key field value of AAA are not deleted.

Step 2 is to insert the aaa records into the database. However this fails with the message that the field is constrained to be unique. The value aaa is already present. An examination of the database shows that this is false. Case sensitivity has not been applied.

The insert query is constructed using the command builder object.

What I have tried:

I have tried appending to the insert query command text " COLLATION Latin1_General_CS_AS" but this didn't help.

How can I construct an insert query so that case sensitivity is respected?

Thanks for any help.
Gerry

Posted 29-Feb-20 18:35pm

Gerry47

Updated 29-Feb-20 23:01pm

Add a Solution

Comments

Jon McKee 1-Mar-20 1:33am

The query and what DB it is would help. Could give COLLATE Latin1_General_BIN a try in the meantime. Your issue is probably a character range specifier like c-e which under the Latin1_General_CS_AS rules includes upper case C and D (alphabetically sorted).

3 solutions

Solution 1

You need to look at the whole of your data: your restraint is not just for a single column containing "aaa" as you have stated that you can have multiple rows with 2aaa" in them - hence that field is not constrained.

However, unless you specify a Primary Key row the whole row must be distinct: so if you are trying to insert three rows with data like this:

AAA 1
aaa 2
aaa 3

You'll be fine, but like this:

AAA 1
aaa 2
aaa 2

It will fail because SQL has no way to check which of the two "aaa" rows you are talking about later.

So check the whole data you are trying to insert, and strongly consider adding an IDENTITY or UNIQUEIDENTIFIER row set as the Primary Key.

Posted 29-Feb-20 19:43pm

OriginalGriff

Comments

Gerry47 1-Mar-20 2:14am

I tried Jon's idea but without success. Thank you Jon.

There is one table, of five, where the key field is the only key field for that table. So there can only ever be one record for a given value of the key field.

For testing I am taking a real value of 2AAFCa3af0cfb and modifying it to 2AAFCa3af0cfbAAA for the initial data upload. This always works. I then modify it to 2AAFCa3af0cfbaaa. This upload should also work, but it doesn't. If I can solve the problem for the table with only one record per unique key field value then I believe that it will be solved for all of the tables.

Solution 2

Found the problem.

The situation is to update an online database with modified data from an offline database.

A datatable was created for each online database table. These tables included the online data and that was the mistake as explained below.

The next step was to write new records uploaded from the offline database to these tables.

Even though the datatables received the Schema of the online database tables, apparently this did not include the case sensitive nature of the first key field.

And that is where the problem lay. By having records in the datatable with keys that clashed with the uploaded data (when case sensitivity was NOT taken into account), the operation to add records failed.

But by adding uploaded data to empty datatables there were no clashes for this step and ultimately the online database was successfully updated.

Sorry for taking your time on a poorly defined problem.
But thanks for your generous help. Much appreciated.

Posted 29-Feb-20 21:28pm

Gerry47

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2020-02-29T22:02:00

Not sure if I understand the situation correctly, but if the uniqueness must be enforced using case sensitive comparison, I would prefer defining that logic to the table. Modifying the insert statement to correctly handle the collation sounds risky.

So why not define the collation for the column that is defined as unique. Consider the following example where CSData column uses case sensitive uniqueness and CIData case-insensitive.

SQL

CREATE TABLE CollateTest (
   CSData nvarchar(100) COLLATE Latin1_General_CS_AS,
   CIData nvarchar(100) COLLATE Latin1_General_CI_AI
);

ALTER TABLE CollateTest 
ADD CONSTRAINT UQ_CollateTest_CSData UNIQUE (CSData);

ALTER TABLE CollateTest 
ADD CONSTRAINT UQ_CollateTest_CIData UNIQUE (CIData);


INSERT INTO CollateTest (CSData, CIData) VALUES ('AAA', 'Test1'); -- succeed
INSERT INTO CollateTest (CSData, CIData) VALUES ('AAa', 'Test2'); -- succeed
INSERT INTO CollateTest (CSData, CIData) VALUES ('AAA', 'Test3'); -- fail

INSERT INTO CollateTest (CSData, CIData) VALUES ('Test1', 'AAA'); -- succeed
INSERT INTO CollateTest (CSData, CIData) VALUES ('Test2', 'AAa'); -- fail
INSERT INTO CollateTest (CSData, CIData) VALUES ('Test3', 'AAA'); -- fail