t-sql problem

Posted on 2009-04-23
Last Modified: 2012-05-06
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....
Question by:innocent1973
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you can do this:
    UPDATE test set ....
    IF @@ROWCOUNT = 0
      INSERT into test ...

    Open in new window

    LVL 39

    Accepted Solution

    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....
    LVL 7

    Expert Comment

    declare @hit  varchar(10)
    Select @hit = ISNULL(hit,'') from test
    if @hit='' then INSERT INTO.......
    if @hit<>'' then update Test1....
    LVL 40

    Expert Comment


    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.
    LVL 3

    Expert Comment


    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})

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now