How to check for any duplicate values before adding a new entry to SQL Server CE database?

1

Hi,
I want to add the new entry to my SQL Server CE but this is done if values of the first and third columns don't exist in the database at the same time. I'm new to SQL queries. I tried the following code but it doesn't work. I think it has a syntax error. Please help me.
insert into MyData values('test01', '1122/035', '666','ty01', 'tt01') where not exists (select * from MyData where 'test01', '1122/035', '666','ty01', 'tt01')

2

I think you need to rewrite your WHERE statement:

insert into MyData values('test01', '1122/035', '666','ty01', 'tt01') where not exists (select * from MyData where column1='test01' AND column2='1122/035' AND column3= '666' AND column4='ty01' AND Column5='tt01')

You can also do this:

IF NOT EXISTS (SELECT 1 FROM MyData WHERE column1='test01'...)
BEGIN
insert into MyData values('test01', '1122/035', '666','ty01', 'tt01');
END

WHERE (Transact-SQL) - SQL Server | Microsoft Docs

EXISTS (Transact-SQL) - SQL Server | Microsoft Docs

1 Like
3

I tested your first alternative. But it gives error:

Error Code: 80040E14
Message : There was an error parsing the query. [ Token line number = 1,Token line offset = 71,Token in error = where ]
Minor Err.: 25501
Num. Par. : 1
Num. Par. : 71
Err. Par. : where

4

You need to be sure that the field names are correct. Maybe you should use [ ] around your column names. If you can tell how your MyData table looks like I can help you in detail, otherwise look at the example in the Microsoft Docs.

5

I used square brackets but the problem exists.
insert into MyData values('test01', '1122/035', '666','ty01', 'tt01') where not exists (select * from MyData where [Wo]='test01' AND [EqN]='1122/035' AND [Code]= '666' AND [Work]='ty01' AND [Cost]='tt01')
Remember that I use SQL CE in C#.
MyData has 5 columns named as Wo, EqN, Code, Work, Cost

6
  1. VALUES does not directly support the WHERE clause
  2. SQL Server CE is depreciated so you might be better looking at something like sqllite.
  3. I am assumng the column names are Col1, Col2, Col3, Col4, Col5. Use your real column names instead.

I cannot remember what subset of SQL is supported in CE but try something like:

INSERT INTO MyData(Col1, Col2, Col3, Col4, Col5)
SELECT 'test01', '1122/035', '666','ty01', 'tt01'
WHERE NOT EXISTS
(
	SELECT 1
	FROM MyData WITH WITH (UPDLOCK, SERIALIZABLE)
	WHERE Col1 = 'test01'
		AND Col2 = '1122/035'
		AND Col3 = '666'
		AND Col4 = 'ty01'
		AND Col5 = 'tt01'
);

or

WITH idata
AS
(
	SELECT *
	FROM
	(
		VALUES('test01', '1122/035', '666','ty01', 'tt01')
	) V (Col1, Col2, Col3, Col4, Col5)
)
INSERT INTO MyData(Col1, Col2, Col3, Col4, Col5)
SELECT Col1, Col2, Col3, Col4, Col5
FROM idata D
WHERE NOT EXISTS
(
	SELECT 1
	FROM MyData M WITH (UPDLOCK, SERIALIZABLE)
	WHERE COALESCE(M.Col1, '') = COALESCE(D.Col1, '')
		AND COALESCE(M.Col2, '') = COALESCE(D.Col2, '')
		AND COALESCE(M.Col3, '') = COALESCE(D.Col3, '')
		AND COALESCE(M.Col4, '') = COALESCE(D.Col4, '')
		AND COALESCE(M.Col5, '') = COALESCE(D.Col5, '')
);

or

WITH idata
AS
(
	SELECT *
	FROM
	(
		VALUES('test01', '1122/035', '666','ty01', 'tt01')
	) V (Col1, Col2, Col3, Col4, Col5)
)
INSERT INTO MyData(Col1, Col2, Col3, Col4, Col5)
SELECT
FROM idata D
	LEFT JOIN MyData M WITH (UPDLOCK, SERIALIZABLE)
		ON D.Col1 = M.Col1
			AND D.Col2 = M.Col2
			AND D.Col3 = M.Col3
			AND D.Col4 = M.Col4
			AND D.Col5 = M.Col5
WHERE M.Col1 IS NULL;

etc

WITH (UPDLOCK, SERIALIZABLE) is theoretically required but may not work with CE.

1 Like
7

SQL CE is still supported this month so I hope you know that :wink: Next month it won't be supported as far as I know. Maybe NOT EXISTS is the problem, you can test it. Maybe this will be a better option:

INSERT INTO MyData([Wo], [EqN], [Code], [Work], [Cost])
SELECT 'test01', '1122/035', '666','ty01', 'tt01'
WHERE NOT EXISTS (
SELECT 1
FROM MyData WHERE [Wo]='test01' AND [EqN]='1122/035' AND [Code]=
'666' AND [Work]='ty01' AND [Cost]='tt01'
)

1 Like
8

I'm going to migrate to SQLight. This code works in SQLight too. Thanks.

9

Is it possible to parameterize your seggested code? I want to use it in C# and read input values from a DataGridView rows (using the For loop in C#). For example:

INSERT INTO MyData([Wo], [EqN], [Code], [Work], [Cost])
SELECT @Wo, @EqN, @Code, @Work, @Cost
WHERE NOT EXISTS (
SELECT 1
FROM MyData WHERE [Wo]=@Wo AND [EqN]=@EqN AND @Code AND [Work]=@Work AND [Cost]=@Cost

And then I link those @Wo, @EqN, @Code, @Work, @Cost parameters into my own C# code. Of course it gives error and need to be revised somehow.

10

This is a great example:

Entity Framework - Insert Update and Delete in C# - YouTube

1 Like