November 12, 2010 at 12:26 pm
I have a special web form for parents.
The form uses parent's email address as login name.
We pulled the email address from our student information system and insert into the table sql table, called users.
Usually one family has two guardians, and in most cases they have their own email address.
But sometimes for some families both parents share one email address.
So when I do an insert into the user table, because the emailaddress is primary key- needs to be unique, it then tells me an error or violate primary key constraints and then the script stops.
But actually I want the script go on to insert next records, how can I do that?
Thanks
November 12, 2010 at 12:32 pm
Hi ann, check books online for TRY...CATCH.
Andreas Goldman
November 12, 2010 at 2:15 pm
Not knowing how you transmit the data from the web application, I am imitating with a temporary table (#T) Prior to what is below, I inserted into my xMast tables CustomerCode column (which is equivalent to your e-mail address column) the value 'abc'. CustomerCode column is defined as the Primary Key.
CREATE TABLE #T(Cod VARCHAR(30))
INSERT INTO #T
SELECT 'jkl' UNION ALL
SELECT 'lmn' UNION ALL
SELECT 'abc' UNION ALL --This is the duplicate entry
SELECT 'opq'
--The actual insert statement
INSERT INTO dbo.xMast(CustomerCode)
SELECT Cod FROM #T T
WHERE NOT EXISTS (SELECT
CustomerCode FROM xMast WHERE T.Cod = xMast.CustomerCode)
I hope this is adaptable to your situation.
November 12, 2010 at 2:27 pm
Thank you all.
I will give a try for the code
November 12, 2010 at 4:12 pm
Ann Cao
Your welcome.
If it works or does not work - come back and let us know .. so others might benefit from your experience
November 12, 2010 at 4:15 pm
Of course if the duplicates are both in the same batch you are inserting -- which sounds extremely likely in the scenario you are presenting -- that code won't solve your problem.
To get around the dups in that case, you would have to modify the input set so that it contains only a single primary key value.
[Btw, the premise of the pk on that table does seem flawed, now. Clearly two people *can* share an email address, so you may want to consider changing the way that table works.]
Scott Pletcher, SQL Server MVP 2008-2010
November 13, 2010 at 8:42 am
taking into account the comment posted by scott.pletcher Posted Yesterday @ 6:15 PM
The folloing additional code should handle possible duplicates in the input data.
--Create prior entries in xMast
INSERT INTO dbo.xMast(CustomerCode)
SELECT 'abc' UNION ALL
SELECT 'def' UNION ALL
SELECT 'ghi'
--SELECT * FROM xMast -- Verify the current contents
CREATE TABLE #T(Cod VARCHAR(30)) --To hold new entries
INSERT INTO #T
SELECT 'jkl' UNION ALL
SELECT 'lmn' UNION ALL --Duplicate entry in input stream
SELECT 'abc' UNION ALL --Entry already present in xMast
SELECT 'opq' UNION ALL
SELECT 'lmn' --Duplicate entry in input stream
--Remove duplicate in new entries
;with numbered as(SELECT rowno=row_number() over
(partition by Cod order by Cod),Cod from #T)
DELETE FROM numbered WHERE rowno > 1
-- select * from #T verify the deletion of duplicates during testing
--Insert statement - from prior posting
INSERT INTO dbo.xMast(CustomerCode)
SELECT Cod FROM #T T
WHERE NOT EXISTS (SELECT
CustomerCode FROM xMast WHERE T.Cod = xMast.CustomerCode)
--SELECT * FROM xMast --test before executing on production DB
DROP TABLE #T
Again, test, test and then test again... before using in production DB
November 13, 2010 at 12:33 pm
set xact_abort off
go
CREATE TABLE #T(Cod VARCHAR(30)primary key)
INSERT INTO #T SELECT 'jkl'
INSERT INTO #T SELECT 'lmn' --Duplicate entry in input stream
INSERT INTO #T SELECT 'abc'
INSERT INTO #T SELECT 'lmn' --Duplicate entry in input stream
INSERT INTO #T SELECT 'opq'
SELECT * FROM #T
DROP TABLE #T
CREATE TABLE #T1(Cod VARCHAR(30)primary key)
INSERT INTO #T1
SELECT 'jkl' UNION ALL
SELECT 'lmn' UNION ALL --Duplicate entry in input stream
SELECT 'abc' UNION ALL
SELECT 'lmn' UNION ALL --Duplicate entry in input stream
SELECT 'opq' UNION ALL
SELECT * FROM #T1
DROP TABLE #T1
GO
See the difference between 2 insert statements above also notice the xact_abort setting.
The first insert statement will allow other records to get inserted even thought there is a constraint error while the second insert will totally discard the whole insert batch.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 13, 2010 at 1:33 pm
Sachin.Nandanwar
From Books On Line: (Emphasis added by this poster)
When SET XACT_ABORT is OFF,in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
Ann Cao
So when I do an insert into the user table, because the emailaddress is primary key- needs to be unique, it then tells me an error or violate primary key constraints and then the script stops.
Since the effect of setting XACT_ABORT is not certain, and as the OP stated that was the case with their present method I elected to remove the duplicates as a more reliable technique to insure that the addition of entries is not thwarted.
November 13, 2010 at 4:24 pm
Thank you all, I think the idea that clean up data first then insert is great. So that there will no primary key violation.
Let me do some test and get back to you.
November 13, 2010 at 11:29 pm
When SET XACT_ABORT is OFF,in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
But since OP has mentioned a specific issue only with primary key violation the severity does not qualify for the whole transaction to be rolled back with the XACT_ABORT setting.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 8:27 am
Ann Cao
Please do come back and post your results, it will help others with similar problems.
Sachin.Nandanwar, the OP stated problem, is NOT the transaction rolling back but:
So when I do an insert into the user table, because the emailaddress is primary key- needs to be unique, it then tells me an error or violate primary key constraints and then the script stops.
But actually I want the script go on to insert next records, how can I do that?
Emphasis added to above quote by this poster.
And the script stopping is the problem I was hoping I could help the OP solve.
November 14, 2010 at 9:47 am
First of all did you test the query I posted earlier?
The first query does exactly what the OP wants.It continues to execute rest of the script even though there is an constraint violating error.
So I wanted that OP gets an idea to create individual insert statement for each record he is inserting
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 19, 2010 at 11:37 am
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply