Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

how can to prevent duplicate inserts for every Execute?

insert into  IQ (XMLDT)values('<row>sdsdf</row>')
share|improve this question
4  
Can we get just a wee bit more code or story to go with this? –  jcolebrand Dec 14 '12 at 18:12
add comment

1 Answer

up vote 4 down vote accepted

Given this table:

CREATE TABLE IQ
(
    XMLDT xml NULL
);

We first need a scalar function to extract the value of interest from the XML:

CREATE FUNCTION dbo.GetRow(@XMLDT AS xml)
RETURNS nvarchar(10)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @XMLDT.value('(./row/text())[1]','nvarchar(10)');
END;

Now we can add a computed column that uses the function:

ALTER TABLE dbo.IQ
ADD RowValue AS dbo.GetRow(XMLDT);

And a constraint to enforce the desired uniqueness:

ALTER TABLE dbo.IQ
ADD CONSTRAINT UQ__IQ_XMLDT_RowValue
UNIQUE (RowValue);

Demo:

-- Succeeds
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');

-- Msg 2627, Level 14, State 1, Line 1
-- Violation of UNIQUE KEY constraint 'UQ__IQ_XMLDT_RowValue'
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.