ignore insertion

Question

Post reply

ignore insertion

Muhammad Furqan

Default port

Points: 1461
More actions
October 1, 2007 at 5:07 am

#177862

i want to insert some data into one table where the data to be inserted is being selected from another table like
insert into table1 select col1,col2 from table2;
what i want to do is that primary key and other constraints must not stop the insertion process, rather it must continue insertion. what can be done??
in mysql, one can write sql like below to achieve it.
insert ignore into table1 select col1,col2 from table2

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You can disable check constraints and foreign keys by executing for each of them the

ALTER TABLE mytable NOCHECK CONSTRAINT name_of_constraint

for the indexes you can disable them (see alter index), but this will not work with the clustered index (if you disable it, it will make your table unavailable.

Question: if you know that your new data will violate the constraints, why do you need the constraints?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Muhammad Furqan Default port Points: 1461 More actions · Answer 2

Well, well !!!

i think i was unclear.

what i meant was that constraint should be enforced, but a constraint being violated should not give an error and terminate the execution, rather it should ignore the error and continue insertion.

For example, if i have a unique constraint on a column COL1, then if, while insertion, that uniqueness constraint is violated, it should ignore that particular row, and continue inserting other rows.

Now, any idea ???

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Unfortunately SQL Server does not ignore errors in insert statements, and you cannot really force it to do so. At the end of the day it is a transaction, and if you tell it it should insert a row, it should either insert it or roll back.

The alternatives what you can have is to select the data into a temporary table, and then select from this the data that does satisfy the constraints by setting the restrictions in the where clause.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Muhammad Furqan Default port Points: 1461 More actions · Answer 4

hmmm, i think thats a good feature in mysql.

anyway !!!

there are other solutions. thanks for that !!!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Replace your primary key with a clustered unique index and use WITH (IGNORE_DUP_KEY).

You'll see information messages about "Duplicate key was ignored", but you won't get errors.

James Goodwin Hall of Fame Points: 3691 More actions · Answer 6

Muhammad Furqan (10/1/2007)
i want to insert some data into one table where the data to be inserted is being selected from another table like
insert into table1 select col1,col2 from table2;

Try something like (assuming col1 is the primary key):

insert into table1

select col1, col2 from table2

where not exist(Select * from table1 t1 where Table2.col1=t1.col1)

JimFive

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Muhammad Furqan (10/2/2007)
Well, well !!!
i think i was unclear.
what i meant was that constraint should be enforced, but a constraint being violated should not give an error and terminate the execution, rather it should ignore the error and continue insertion.
For example, if i have a unique constraint on a column COL1, then if, while insertion, that uniqueness constraint is violated, it should ignore that particular row, and continue inserting other rows.
Now, any idea ???

Create the unique constraint with the IGNORE DUPES option... it's it Books Online... everything will be automatic for you then.

Slow way to do things, though... best thing to do is write the proper code to do a proper insert that checks to make sure the data being inserted doesn't violate the constraint.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)