• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

t-sql problem

Hello Experts,
Please correct my mistake.I am trying to execute the following query:

Select hit from test
if hit is NULL then INSERT INTO.......
if hit is NOT NULL then update Test1....
0
innocent1973
Asked:
innocent1973
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do this:
UPDATE test set ....
IF @@ROWCOUNT = 0
  INSERT into test ...

Open in new window

0
 
appariCommented:
can you give more details?
from my understanding you were trying to get value of hit and if it is null insert else update, is this is the question?
try this one.

declare @hit  varchar(10)
Select @hit = hit from test
if @hit is NULL then INSERT INTO.......
if @hit is NOT NULL then update Test1....
0
 
luaniCommented:
declare @hit  varchar(10)
Select @hit = ISNULL(hit,'') from test
if @hit='' then INSERT INTO.......
if @hit<>'' then update Test1....
0
 
SharathData EngineerCommented:

What do you mean by "select hit from test"
hit is a column in your table having NULL and non-NULL values if it is NULLable column. Then what is your criteria to run INSERT/UPDATE statement.
Do you want to execute INSERT statement, if you have atleast one NULL value in this column?
eloborate your requirement more.
0
 
GSQLCommented:
innocent1973

based on your requirement, i assume that you sometimes expect no result from your query of "test" in which case you want to insert data into "test1".

if, however, you have only 1 table (ie, only a table called "test" which you will update if the data exists in "test" and otherwise insert data into "test"), then angelIII has an elegant solution.

but supposing my assumption is good; if you have 2 tables (test and test1) and you are testing something in "test1" in order to decide whether to insert or update "test", then consider

declare @hit bit ; select @hit = isnull((select 1 from test where {YourHitCriteriaHere}), 0) -- either 0 or 1
update test1 set YourColumnHere = 'NewValue' where {YourUpdateCriteriaHere} and @hit = 1
if @@rowcount = 0
insert test1 ({yourcolumnlist}) values ({YourValues})
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now