How to avoid duplicate records when using datainsert?

2016 1 21
1 个回答
回答已采纳
更新时间：2016 1 22
7 次查看（30 天）

0 个投票

datainsert fails when it encounters any duplicate records. Is there a way to specify it should just skip the duplicates, perhaps returning an index of the successful inserts?
datainsert(conn,'consdata',colnames,tbl)
Error using database.ODBCConnection/insert (line 264)
ODBC Driver Error: ERROR: duplicate key value violates unique constraint "consdata_pkey";
Error while executing the query

请先登录，再回答此问题。

 采纳的回答

Roger Wohlwend
Roger Wohlwend 2016-1-22

0 个投票

No, you can't specify something like that. If you want datainsert to continue with the next dataset if it encounters a duplicate, just use try-catch.
try
datainsert(conn,'consdata',colnames,tbl)
catch E
% This section is only executed if a duplicate record exists. So if you want
% Matlab to remember when datainsert failed because of a duplicate record,
% put some code here. For expample if you execute this try-catch within a
% Loop, write the Loop index to a vector.
end

3 个评论
显示 1更早的评论 隐藏 1更早的评论

Neil Caithness
Neil Caithness 2016-1-22
编辑：Neil Caithness 2016-1-22
Thanks Roger,
So the options for bulk inserting 250,000 records don't look attractive.
1. Pull all the existing records from the db and check for duplicates using setdiff (I don't fancy the performance).
2. Loop over the set and do one at a time catching the errors as you suggest.
Or is there something more attractive?
Regards Neil
Neil Caithness
Neil Caithness 2016-1-22
编辑：Neil Caithness 2016-1-22
I guess I could use datainsert into an empty table with no primary keys to obstruct the process, and then do an sql insert into the target table.
INSERT INTO table1
(SELECT * FROM table2
WHERE name NOT IN
(SELECT name FROM table1));
Maybe the performance of this won't be too bad.
Now I really need an answer to my second question Does the Database Toolbox support sql numeric array data types ?
Neil Caithness
Neil Caithness 2016-1-22
For completeness I thought I'd add this just found in the PostgreSQL 9.5 documentation:
ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — "UPDATE or INSERT".
I'm guessing this will be the best of the available options.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Export Data Programmatically 的更多信息

产品

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by