Check - 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

General SQL Server Forums

New to SQL Server Programming

Check

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-05-05 : 09:43:33

Hi i have an insert query statement in the format

"INSERT INTO dbo.eli_mat(ProgramID,ClaimStatus,ClaimSubStatus,ClaimType,ClaimResult,IsActive)

SELECT @ProgramID,'O','ACCEPT','MEDI',1,1
UNION ALL
SELECT @ProgramID,'O','ACCEPT','MANA',1,1
UNION ALL
SELECT @ProgramID,'O','ACCEPT','INDE',1,1
UNION ALL
SELECT @ProgramID,'O','DENY','MEDI',0,1"

i need to check whether the column which i have in the insert statement namely(ProgramID,ClaimStatus,ClaimSubStatus,ClaimType,ClaimResult,IsActive) already has the value "@ProgramID,'O','ACCEPT','MEDI',1,1
"(similarly the other select statements also)if it has it should not take those values if it doesnt have only it should take to do union with other things.Can anyone help me urgent

susan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-05 : 09:47:02

You can try to add WHERE NOT EXITS

No, you're never too old to Yak'n'Roll if you're too young to die.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-05-05 : 09:53:26

cAN U FRAME AND SHOW ME HOW TO MODIFY THE QUERY PLS

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 09:58:38

Do you want the insert to fail if values in any one of those selects are already in the table ?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-05-05 : 10:03:15

yes

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 10:31:49

something like this,

if exists
(
SELECT 
	*
FROM 
	dbo.eli_mat
	JOIN 
	(SELECT
		col1,col2,...
		union all
	 SELECT
		col1,col2,...
		union all
	 SELECT
		col1,col2,...
		union all
	)s on s.col1=eli_mat.col1 and s.col2=eli_mat.col2 and .....
)
RAISERROR ('already exists',16,1)

ELSE

insert into eli_mat
SELECT
		col1,col2,...
		union all
	 SELECT
		col1,col2,...
		union all
	 SELECT
		col1,col2,...
		union all

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-05-06 : 01:34:21

Thank you i got it

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources