Troublesome INSERT Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Troublesome INSERT Statement

Author

Topic

Pistris
Starting Member

5 Posts

Posted - 2008-02-27 : 10:10:00

Hey All,

There are three tables in my small database:

tblState
- ID (primary key)
- State

tblQuestion
- ID (primary key)
- Question

tblAnswer
- ID (primary key)
- QuestionID
- StateID
- Answer

How do I structure an INSERT statement to allow inserting an Answer only when the the unique combination of StateID & QuestionID does not already have an Answer?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 10:14:45

quote:
Originally posted by Pistris

Hey All,

There are three tables in this small database:

Yes, but where is the INSERT statement to troubleshoot?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 10:51:30

CREATE UNIQUE NONCLUSTERED INDEX index_name ON tblAnswer ( QuestionID, StateID )

Pistris
Starting Member

5 Posts

Posted - 2008-02-27 : 15:21:09

Thanks jdaman. I've created that index in tblAnswers. Now I'm not sure how to query against the index in my SQL statement.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 15:52:01

The index will prevent duplicate QuestionID and StateID records from being inserted into the table. You can now wrap your insert statement into a TRY CATCH block like so:

BEGIN TRY
INSERT...
END TRY
BEGIN CATCH
PRINT 'Already Exists in table.'
END CATCH

or you can query the table initially before your insert with an if statement like so:

IF NOT EXISTS ( SELECT 1 FROM #Table WHERE QuestionID = @QuestionID AND StateID = @StateID )
BEGIN
INSERT...
END
ELSE
PRINT 'Already Exists in table.'
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-27 : 16:37:32

[code]
insert into tblAnswer
(
QuestionID,
StateID,
Answer
)
select
QuestionID = @QuestionID,
StateID = @StateID,
Answer = @Answer
where
not exists (
select *
from
tblAnswer
where
QuestionID = @QuestionID and
StateID = @StateID
)
[/code]

CODO ERGO SUM

Pistris
Starting Member

5 Posts

Posted - 2008-02-27 : 17:43:04

Thanks jdaman & Michael. Exactly what was needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources