Post reply

ignore insertion

  • 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

  • October 1, 2007 at 6:50 am

    #737677

    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

  • October 2, 2007 at 1:54 am

    #738035

    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 ???

  • October 2, 2007 at 2:29 am

    #738044

    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

  • October 2, 2007 at 4:11 am

    #738056

    hmmm, i think thats a good feature in mysql.

    anyway !!!

    there are other solutions. thanks for that !!!

  • October 2, 2007 at 5:54 am

    #738092

    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.

  • October 2, 2007 at 8:24 am

    #738186

    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

  • October 6, 2007 at 2:27 pm

    #739814

    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)
    Intro to Tally Tables and Functions

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

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