Insert into using a where clause

Hi
Does anyone know whether this is possible.

I want to insert 2 values into a table, only if 1 of the values match the value in a table column

i.e
@Number VarChar(100),
@Code VarChar(400)
AS
insert into mytable
(NumberValue, CodeValue)
Values(@Number,
@Code)
Where NumberValue <> @Number and CodeValue = @Code

The above will not work and I do not know how to produce the code that will.

Any help would be appreciated.
Caz

BeginningWebDesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
Try putting an if on it.

declare @Number int, @Code int
select NumberValue, CodeValue from mytable where NumberValue <> @Number and CodeValue = @Code

if @@rowcount > 0
begin

insert into mytable (NumberValue, CodeValue)
Values(@Number,@Code)

end

Cheers
0
BulZeyECommented:
What about:

Insert into Mytable
  (NumberValue, CodeValue)
select top 1 @Number, @Code
 from MyTable
where NumberValue <> @Number
and CodeValue = @Code
0
sigmaconCommented:
You need to check two things: Does the CodeValue exist? If so, you need to make sure that a row with that number for that code does NOT exists. I tried to combine the statements, but they have a counter-point: if the code does not exist, neither does the row with the number value for that code:

...
@Number VarChar(100),
@Code VarChar(400)
AS

if exists ( select top 1 * from mytable Where CodeValue = @Code )
  and not exists ( select * from mytable Where NumberValue = @Number and CodeValue = @Code )
     insert into mytable ( NumberValue, CodeValue )
     Values ( @Number, @Code )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imrancsCommented:
OR

INSERT INTO Mytable  (NumberValue, CodeValue)
SELECT @Number, @Code
WHERE NOT EXISTS(SELECT * FROM MyTable NumberValue <> @Number and CodeValue = @Code)


Imran
0
imrancsCommented:
My suggestion is little modified version of one suggested by BulZeyE above.

Imran
0
sigmaconCommented:
Both BulZeyE and imrancs version will allow to insert rows that based on your initial statement you did not want to have inserted. If you don't care about duplicate rows in your table, you can ignore the rest of this post.

Lets say you have this in your table:

NumberValue      CodeValue
1                         'bla'
1                         'doh'
2                         'bla'
2                         'doh'

If you now try to insert a row with @number = 1 and @code = 'bla', it will insert, because there is a row that fullfills

NumberValue <> @number and CodeValue = @code

It's the row with NumberValue = 2 and CodeValue = 'bla'

For that reason, you need two checks: One for whether the CodeValue already exists in the table and one for making sure that a row with a given number for the code does not yet exist.
0
BeginningWebDesignAuthor Commented:
Thanks Everyone

Code works in Query but when tested on site does not work, I'm working on the problem

Caz
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.