|
Can anyone please check this SQL CE query and revise its syntax for me?
insert into MyData values('test01', '1122/035', '666','ty01', 'tt01') where not exists (select * from MyData where 'test01', '1122/035', '666','ty01', 'tt01')
In query above, I add a new row to SQL data but it checks for duplicates before adding.
modified 5-Jun-21 2:45am.
|
|
|
|
|
There is no such thing as a WHERE clause on an INSERT statement.
You have to write this as two queries. The first is the SELECT to see if anything is returned. If there isn't, then you can execute the INSERT query.
|
|
|
|
|
Quote:
I found a solution on the internet which fixes it. You can use a command of the form:
INSERT INTO TableName (ColumnName)
SELECT '" + value + "' WHERE NOT EXISTS (
SELECT ColumnName from TableName WHERE Name = '" + value + "')";
Followed by an ExecuteNonQuery().
SQL Server Equivalent of MySQL INSERT IGNORE
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
My user needs to load some excel sheets and store some of their information into a SQL CE file. This SQL CE file contains a table with 5 columns. The rows will increase as the user adds their information into SQL. What is the best way to do this? Is there any SQL query that can check for duplicate values and if the new entry does not exist in the SQL file, adds it to the table?
|
|
|
|
|
No, there is no query statement that will do that for you.
You could write up a stored procedure to do that in the database and call that stored procedure from your app with the appropriate data.
|
|
|
|
|
I added my method below. Please read it.
|
|
|
|
|
I never used SQL CE (compact edition)
Dave Kreskowiak wrote: You could write up a stored procedure to do that in the database and call that stored procedure from your app with the appropriate data.
, however, I always thought the stored procedures are not available there... Was I wrong? 
|
|
|
|
|
I really don't know. I don't use CE at all.
After looking it up, SP's aren't supported, and neither is multi-user. awesome...
|
|
|
|
|
SQL CE is the same as SQL Lite. It's a "local" db for an app. Allows you to use SQL / EF without a "server". It's not meant to be shared though you can easily copy it (the single db file).
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I used a method that can do this work, but it gets slower as the data increases.
I have used Dictionary<string, string=""> class to detect duplicate values (Dictionary.ContainsKey()).
After that I take them into a DataTable and then into SQL CE file.
Each time I want to add new data into my SQL, I read all previous SQL data into a DataTable then take them into Dictionary<> and check new data for any duplicate values and if they were unique, adding process is continued as above.
The problem is that this method works fine and fast to some extent. As the data increase(for example more than 5000 rows in SQL), it gets some seconds to calculate the process.
modified 2-Jun-21 13:19pm.
|
|
|
|
|
And this is why I said do it in a stored procedure in the database.
For every record you want to insert, why on earth would you read all of the existing data? Yes, this get's slower and slower every time you insert a record. All that data transfer will take more and more time.
It sounds like you're not even caching the data you read and insert! If you're going to do something like this, you would normally just read the database once, keep all that data, then add new records to the cache and have the cache update the database.
This has a downside though. You really cannot use it in a multi-user environment, and there is a limit to the amount of data you can keep in memory, so it doesn't scale well at all.
Doing it in the database removes reading all that data over and over again, and removes the memory size limits.
DO IT IN THE DATABASE WITH A STORED PROCEDURE!
|
|
|
|
|
Is this the way to DO IT IN THE DATABASE WITH A STORED PROCEDURE:
When I want to add a new entry, I should use a query to check if that entry exists in SQL. If it was unique, I would add it to SQL database.
If it was not what you meant, please explain your method for DOING IT IN THE DATABASE WITH A STORED PROCEDURE more.
Thanks.
|
|
|
|
|
You don't know what a stored procedure is, do you?
Yes, you can do it by querying the database for the record you want to insert. If it's already there, you can either update that record or ignore it and go to the next record in your Excel sheet. If it's not there, you can insert it.
Again, there's a problem with doing it from the client-side. In a multi-user environment, if someone else does the same thing at the same time, you could end up with the same record being updated by two or more clients at the same time, or inserting the same record at the same time.
|
|
|
|
|
Thanks, My application is single-user.
|
|
|
|
|
CE doesn't support SP's, nor is it multi-user.
So, you can query the database from your app for the record you're trying to add.
|
|
|
|
|
He's anti-SQL. Doesn't know it; doesn't want to learn it. Every solution is C# only; even if it's a database app.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Well then, he's really screwing himself, isn't he? 
EDIT:
Why did I just get the feeling he's going to be coming back bitching about more performance problems because he doesn't have any indexing setup?
|
|
|
|
|
He won't accept that without "SQL" everything has to be downloaded to the client; that's why he'll always have performance issues.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
No, I'm serious about my work. I just want to learn.
|
|
|
|
|
yes, I don't have enough knowledge about SQL query but this does not mean I hate it or I don't want to learn it. I'm trying to learn it now. Please don't leap to the conclusion like this.
|
|
|
|
|
Alex Dunlop wrote: I'm trying to learn it now.
Then stop wasting time with questions here, and make use of one of the myriad tutorials on the subject: SQL Tutorial[^].
|
|
|
|
|
Create a unique index on the table:
CREATE INDEX (SQL Server Compact) | Microsoft Docs[^]
If the majority of values will be new, then just insert them and handle the constraint violation error which will be thrown when a duplicate value is inserted.
Otherwise, issue an UPDATE for the row and check the number of rows affected. If no rows were affected, issue an INSERT for the row.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm using advanced DataGridView (advDataGridView) in my project. it has Filter and Sorting capability. When my data rows is high enough, for example, more than 10000 rows, It takes some seconds to filter the string I'm searching. It's too laggy for dense DGV data. Is there any way to increase the string search speed? (Search as the speed of Excel filter)?
modified 30-May-21 5:07am.
|
|
|
|
|
A DataGridView is a visual control so every time you change something it needs to reorganise its content before refreshing the display, and that takes time. And the more rows you have the longer it takes. Putting more than around 100 rows on such a control at any one time is not a good idea. Use some backing control to do the filtering and sorting, and page the display. Do you really think your users want to try scrolling through thousands of rows?
|
|
|
|
|
As Richard has said, it's a bad idea to even try and throw that much data at users - all that will happen is they will stop using your app, and / or demand their money back. I would!
Filtering can be speeded up on a standard DataGridView by using a filter in a BindingSource instead of sourcing the data directly from a DataTable or similar - but you'd have to see if your control supports that.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
