Ignore & Continue in SQL Server Queries

Question

0.00/5 (No votes)

See more:

Hello Experts

Consider the below Database:

SQL

Create	Database	Test
Go
Use	Test
Go
Create	Table	table1	(
ID	BigInt	Identity	Not Null	Primary Key,
Title	nVarchar(50)		Not Null	Unique
)
Go
Insert	table1	(Title)
	Values	(N'title1')
Go

Here is my question that what code I can use to log the incompatible recordes and continue with correct records.
For example consider this query:

SQL

Insert	table1	(Title)
	Select	N'title2'
		Union
	Select	N'title1'

As you know, no record will affect.
Is there any query that inserts the 'title2' and Prints the inserting error of 'title1' ?

Posted 15-Nov-13 21:11pm

Meysam Toluie

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-11-16T11:26:00

Your issue is not clear enough.

First of all, i would suggest you to read below articles:
Error Handling in T-SQL: From Casual to Religious[^]
Using TRY...CATCH in Transact-SQL[^]
SQL Server Error Handling Workbench[^]
Handling Errors in Stored Procedures[^]
SQL Server 2005 Try and Catch Exception Handling[^]

Secondly, you can check for existing records in a 3 ways:
1) using EXISTS[^] statement.
2) using SELECT with IN[^] or NOT IN option
3) looping through the set of records (using WHILE[^] statement or CTE[^] /Common Table Expressions/)

Example:

SQL

INSERT INTO Table2 (Title)
SELECT Title
FROM (SELECT 'Title1' UNION ALL
      SELECT 'Title2' UNION ALL) AS T
WHERE T.Title NOT IN (SELECT Title FROM Table2)

NOTE: Above query is not optimal and time of execute is very long in case of large data amount.