BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?

Jun 22, 2007

All,

Just want to make sure that I understand what's going on here.

I have a table with IGNORE_DUP_KEY set on a unique, multi-column
index.

What I'm seeing is this:

1) When performing a BULK INSERT, the UNIQUE index is not being
respected and rows which violate the unique index are inserted.

2) When performing a regular INSERT, the UNIQUE index is being
respected and rows which violate the unique index ARE NOT inserted.

Is this expected behavior.

Also, I have some questions, given the index described.

Q1) Will a regular INSERT that attempts to insert duplicate data get
an error back or just a warning?

Q2) How can I set things up so that a BULK INSERT would NOT allow
duplicates to be entered into the table?

Thanks,
Wes Gamble

View 2 Replies


ADVERTISEMENT

Unique Index Conflict On Insert Into

Jun 18, 2008

I get this message when doing an insert into :

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.

Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?

If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

View 6 Replies View Related

Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'

Feb 28, 2007

I have 1 client who keeps running into the following error on the subscriber and merge agents >

€śCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?

Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.

Thank you in advanced,
Pauly C

View 6 Replies View Related

Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.

Aug 22, 2007

Hi.

I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1

But, now I am getting the below error:

Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.

Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.

Any suggestions as to where I messed up and how to deal with this?

Thanks.


DBO.USERS






Code Snippet

CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [varchar](10) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON


DBO.SAVEDSEARCHES


CREATE TABLE [dbo].[savedsearches](
[savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
[searchname] [varchar](50) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NULL,
[isdefault] [bit] NULL,
[gender] [char](10) NULL,
[startyear] [varchar](50) NULL,
[endyear] [varchar](50) NULL,
[country] [varchar](50) NULL,
[miles] [int] NULL,
[pictures] [varchar](50) NULL,
[postal] [int] NULL,
[sort] [tinyint] NULL,
[photostring] [varchar](50) NULL,
[orderby] [tinyint] NULL,
CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
[userid] ASC,
[searchname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO




The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.

INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))

DBO.LASTLOGIN





Code Snippet


CREATE TABLE [dbo].[lastlogin](
[lastloginid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)),
[activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()),
[online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]

View 1 Replies View Related

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At

Jan 16, 2007

We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.

I have the following problem

I have applied Nonclustered index over a column on a table.

Designed a SP for insertion which caters for updation incase the criteria based on the input is met.

The logic goes like this

Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.



However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.

Can any body tell me if there is any problem with the SQL Server 2005







Thanks in advance

R Suresh, SQLDBA







View 8 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

Apr 18, 2008



Hello,

I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.

Thanks.

View 8 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View 1 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View 2 Replies View Related

Unique Index Vs Unique Constraints

Mar 26, 2008



hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?

View 12 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

Bulk Insert Using Script And Not Bulk Insert Task

Nov 2, 2007



Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.

View 6 Replies View Related

Bulk Insert - Bulk Load Data Conversion Error

Jan 17, 2008

Im having some issues with bulk insert.

This is the table:

CREATE TABLE [dbo].[tmp_GA_status](

[GA_recno] [int] NOT NULL,

[GA_desc] [varchar](40) NULL

)


This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"


and this is the sql:

bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'

with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')



so yeah, pretty simple. But whatever I do I get this;

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).



So what am I doing wrong ?

View 13 Replies View Related

I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?

Sep 27, 2007

I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.

Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?

Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!

View 1 Replies View Related

Cannot Fetch A Row From OLE DB Provider BULK With Bulk Insert Task

Nov 23, 2005

Hi, folks:

View 18 Replies View Related

Pros: How To Bulk Delete And Bulk Insert?

Oct 11, 2000

I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)

Updates and cursors just seem to be too slow.

So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.

This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert


Any comments are appreciated,

-Marc

View 3 Replies View Related

IGNORE_DUP_KEY Performance Problem

Mar 1, 2007

I have a performance problem using ignore_dup_key='on' on SQL Server 2005
(with or without SP2).

The scenario is that we are collecting high volume real time data that
contains duplicates.
Currently the table contains about 60 million rows (including duplicates) and growing, but about
30% of that is duplicate data.
It's envisaged that we will eventually be collecting 20 million rows per day.

We currently have an index on the table to assist with data retrieval so I
removed all the duplicates and
extended this index to cover all the rows and made it unique to prevent
duplicates.

I did some performance tests that revealed there very little performance
hit, if any, from doing this.

Here's the index definition:

CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[BILLINGREGISTER]
(
[METERPOINT_ID] ASC,
[DATETIME] ASC,
[METERDATATYPE_ID] ASC,
[UNITS_ID] ASC,
[ALARM_FLAGS] ASC,
[VALUE] ASC
)
INCLUDE (
[EXPORTED]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The data is inserted in batches of about 20-60 rows like this:

begin tranaction (using TransactionScope) (read committed isolation
level)
get data from MSMQ
access a couple of tables
create batch SQL
"insert into Table (...) values (... )
insert into Table (...) values (... )
insert into Table (...) values (... )
insert into Table (...) values (... ) etc "
execute batch SQL
commit transaction

On my hardware this test runs at about 17 batches/second with one client
thread, and at about 71 batches/second with 10 client threads.

For the test I made sure there were no duplicates.

So now I set IGNORE_DUP_KEY = ON and retry the tests. Performance drops to
2.5 batches/second for both 1 and 10 client threads!

Why is there this such a massive performance hit?

Thanks for any help.
Phil Lee

View 8 Replies View Related

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

Apr 8, 2008


I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:


Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".

Task failed: Bulk Insert Task

In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');


What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}

Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul

View 1 Replies View Related

Unique Index

Mar 29, 2004

Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index.

But effectively they do the same thing.

View 3 Replies View Related

Unique Index

May 8, 2008

How to findout whether a Index is unique or not?

------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Unique Index

Mar 26, 2007

if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob.

Question is, how to construct a unique identifier from those components. If not unique, then at least usable?

Again, if this post is wrong here, I apologize

Thanks for any input

Walter

View 1 Replies View Related

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')

[code]...

View 5 Replies View Related

Can't Add Bit Column To Unique Index

Feb 6, 2007

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.

View 5 Replies View Related

Better Performance With Unique Index?

May 21, 2002

I have read that you get better performance with unique indexes rather than non-unique indexes.
I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan.
I even tried clauses such as
WHERE 1 < (SELECT COUNT(*) FROM TheTable
WHERE key_column = OtherTable.key_column)
which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not.
I have also tried a unique constraint instead of a unique index and got the same (non)results.
Can anyone come up with an example where creating a unique index actually makes a performance difference?

View 1 Replies View Related

UNIQUE INDEX If Not NULL

Dec 17, 2006

Hello !

for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL

CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]

i can have 100 rows with a NULL Serial


thank you

View 9 Replies View Related

Unique Index On 2 Columns?

Apr 19, 2013

I find to be able to have multiple NULL entries in the following constraint:

Code:

CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;

But is not possible to check multiple columns in "WHERE".

I using SQL Server 2012 Express

View 3 Replies View Related

Unique Constraint/index

Feb 12, 2004

I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes

Thanks in advance

View 7 Replies View Related

Unique Constraint/Index

May 7, 2008

What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?

------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved