Jeff,

How can I get the number of rows failed after call exec(@sql)? I need this number to inform client if any error happen.

And also, I wish if one row get error,just skip this row,continue insert other rows.
Is this default properties?

Do need to to set value for MAXERRORS for my purpose?

You would need to set MaxErrors.

--Jeff Moden

Sorry I have a lots of questions.

When I insert data from temp table to real table, if the row already exists in real table, don't input the row from temp table to real table,

Do the following codes do what I wish?

CREATE TABLE #tempTbl( -- same field and same order as real table
   STR_ID numeric(18, 0) NOT NULL, --Primary key
   COMPID numberic(18,0) NOT NULL, --Primary key
   TITLE VARCHAR(50),
   PFIELD VARCHAR(50),
   PDESC VARCHAR(50)
)

INSERT realTabl (STR_ID, COMPID, TITLE, PFIELD, PDESC)
SELECT * ----1: is it ok here like this
FROM #tempTbl src
WHERE NOT EXISTS (SELECT * '----2: is it ok here like this
FROM realTabl tbl
WHERE tbl.STR_ID = src.STR_ID and tbl.COMPID = src.COMPID)

Thanks!

Looks like it... I just hate correlated subqueries because they have to do a SELECT for every row that needs to be found... that's as bad as a cursor. That's why I did it like I did in example #3.

--Jeff Moden

Yes, Jeff, I also don't like above codes I posted above. ok now I understand why you use WHERE p.SomeID IS NULL in your codes, since after left outer join, if the row is not in my real table then p.SomeID IS NULL. so we only insert all the rows with p.SomeID IS NULL to my real table, did my understanding right?

Now I set MAXERRORS=1000 for BULK INSERT.

Jeff,

1: would you please tell me how I can get the number of rows failed?
2: can I modify your codes as the following:

INSERT INTO realTable
        (SomeID,SomeDesc)
 SELECT * ------------------------use * ok or have to list all fields? s.SomeID, s.SomeDesc
   FROM TempTbl s
   LEFT OUTER JOIN realTable p
     ON s.STR_ID = p.STR_ID and
        s.COMP_ID = p.COMP_ID
  WHERE p.STR_ID IS NULL

Thanks for your help and your time!

Given that the TempTbl was created *from* the realTbl, you should be okay using
    SELECT *
but if you do that then you should *OMIT* the list of fields after the INSERT INTO that you show there. Thus:
    

Thanks, Old Pedant.

How about get the number of rows failed? I need this number, if any row failed I will tell client to recheck the data.