[dba-SQLServer] SQL Server - Append records without fail

And BTW this goes for both plain unique indexes and for primary keys as demonstrated below:

-- Unique index:
CREATE TABLE T1(C1 int)
GO
CREATE UNIQUE INDEX UI_C1 on T1(C1)
GO
INSERT INTO T1 VALUES(3)
INSERT INTO T1 VALUES(4)
INSERT INTO T1 VALUES(5)
INSERT INTO T1 VALUES(6)
GO

CREATE TABLE T2(C1 int)
GO
INSERT INTO T2 VALUES(1)
INSERT INTO T2 VALUES(2)
INSERT INTO T2 VALUES(3)
INSERT INTO T2 VALUES(4)
INSERT INTO T2 VALUES(7)
GO

INSERT INTO T1 SELECT C1 FROM T2 -->Error
SELECT * FROM T1-->All inserts cancelled
GO

ALTER INDEX UI_C1 ON T1 REBUILD WITH (IGNORE_DUP_KEY = ON)
GO
INSERT INTO T1 SELECT C1 FROM T2 -->Warning: Duplicate key was ignored
SELECT * FROM T1-->All inserts succeeded except for those creating key-duplicates, behaviour as in Access

-- Primary key:
CREATE TABLE T3(C1 int CONSTRAINT PK_C1 PRIMARY KEY)
GO

INSERT INTO T3 VALUES(3)
INSERT INTO T3 VALUES(4)
INSERT INTO T3 VALUES(5)
INSERT INTO T3 VALUES(6)
GO

INSERT INTO T3 SELECT C1 FROM T2 -->Error
SELECT * FROM T3-->All inserts cancelled
GO

ALTER TABLE T3 DROP CONSTRAINT PK_C1
GO
ALTER TABLE T3 ADD CONSTRAINT PK_C1 PRIMARY KEY (C1) WITH (IGNORE_DUP_KEY = ON)
GO
INSERT INTO T3 SELECT C1 FROM T2 -->Warning: Duplicate key was ignored
SELECT * FROM T3-->All inserts succeeded except for those creating key-duplicates, behaviour as in Access

Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 2. maj 2011 23:24
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] SQL Server - Append records without fail

OK, good to know!

Thanks,

John W. Colby
www.ColbyConsulting.com

On 5/2/2011 5:18 PM, Asger Blond wrote:
> John,
> If the unique index is created with the option IGNORE_DUP_KEY then the behaviour will be the same as in Access.
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 2. maj 2011 22:27
> Til: Access Developers discussion and problem solving; Sqlserver-Dba
> Emne: [dba-SQLServer] SQL Server - Append records without fail
>
> In access you can append records into a table and if a given record fails, the rest go in.  I use
> that as a quick and dirty filter sometimes when (for example) appending records from one place to
> another.
>
> AFAICT SQL Server will not append any of the records if any single record fails to append, which has
> always seemed strange to me.  It's almost like an unrequested rollback.
>
> Is there any way to make SQL Server accept the appends that will go in and only reject the ones that
> will not for some reason?
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com