How to ignore sql errors? 1

[jsteph]

Hi all,
In Query Analyzer, I'm dumping records from table1 into table2. They are of similar makeup, with similar primary key. I know that some of the data from table1 already exists in table2. I don't care about this.

I want to run a simple query *without* any time-consuming, complex criteria, that will just attempt to dump all records from 1 into 2, and the ones that are dups--who cares--ignore the error message and complete the statement and put in the records that it can.
What syntax or QA configuration would I use to do this?
Thanks,
--Jim

 

[RiverGuy]

INSERT INTO Table1 SELECT * FROM Table2 b WHERE NOT EXISTS (SELECT * FROM Table1 a WHERE a.KeyCol1 = b.KeyCol1 AND a.KeyCol2 = b.KeyCol2, etc for all key columns)

 

[jsteph]

Riverguy,
Thanks, but that's not what I'm looking for--I don't want criteria at all.

What I'm looking for is a way to have the sql engine *ignore* record-by-record PK violation errors and continue the query and insert the records that it can. This is possible in other systems, I'm sure there's just a setting or keyword that I can use.
Thanks,
--Jim

 

[AlexCuse]

jsteph - google search for UPSERT, remove the UP part, and thats' what you will get (still minimal criteria involved though). Something to note, this will require row-by-row processing, a la Oracle, which SQL Server is not really built for. If you are talking about any serious amount of data moving around, I think you might be best served to suck it up and use some criteria so that you can implement a set-based solution. RiverGuy's will work, or there is this:

INSERT INTO Table1 
SELECT * FROM Table2 b
LEFT JOIN Table1 c
on b.PK_COLUMN = c.PK_COLUMN
and b.OTHER_PK_COLUMN = c.OTHER_PK_COLUMN
WHERE c.PK_COLUMN is null

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLBill]

If it is an identity column, you can use:
SET IDENTITY_INSERT tablename ON

Or you can drop the Primary Key.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

you can't ignore it because it is a non trapable error and your statement will be rolled back since you are doing a batch insert

having a where clause is not time consuming, really how long will it take to write that? 20 seconds? You did say you are dealing only with 2 tables.

you have spend way more time replying to this thread than it would have taken you to write the WHERE clause

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jsteph]

SQLDenis,
Thank you, and I understand your point, but the question was both academic and practical--if this was for just one query, you'd be spot on--but there are literally hundreds of tables in this project alone, and certainly more to come in the future.

It's part of an archiving project, and there are so many tables to do and I just want a quick & dirty dump and if the record's already been archived, then let it be. Plus, the source db is not one of which I have control, so the deleting part of the archive process is not something I can control--hence my need to run the archive 'wide open'--the previous day's set of data may not be deleted yet, but I can't afford to try and sync the times with the remote vendor because they are 'not on a schedule', and less than reliable.

So bottom line, I understand the concept of what you're saying, but believe me, I will spend days and days just writing the sql for this project. In my day's with Access, you could just clear some sort of 'fail on error' flag and it would just run the query, and return the # of successful inserts and the # of failed, along with the general reason they failed.
--Jim

 

[SQLDenis]

the only way is to do a batch process and run every row insert separate then if it fails who cares, this of course will be very slow

either option includes writing a bunch of code

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jsteph]

Denis,
Thank you, I've opted to go with the Not Exists() syntax. I have a metadata table with the table names and one with the keys, so I'm writing code to generate the proper sql...I figure it should take a half hour of code to generate the sql for roughly 200 queries, so I should be time ahead still.

BTW, can you tell me which syntax is better and why:
"Select * from Table WHERE Key NOT IN(Select Key from OtherTable)"
or
"Select * from Table a WHERE NOT EXISTS (Select b.key from OtherTable b where b.key = a.key)"

Thanks,
--Jim

 

[SQLDenis]

not exisst is better because of possible null values

read more here

http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[monksnake]

That's very useful Denis, have a stizzzzzar.

I wonder who writes such a website??

<.

 

[ESquared]

jsteph, that's exactly what I was going to suggest: write code that writes (and possibly executes) code. It seems the best overall solution.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]