Solved

Insert into using a where clause

Posted on 2004-10-22
13,751 Views
Last Modified: 2009-12-14
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

0
Question by:BeginningWebDesign
    7 Comments
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    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
     
    LVL 4

    Assisted Solution

    by:BulZeyE
    What about:

    Insert into Mytable
      (NumberValue, CodeValue)
    select top 1 @Number, @Code
     from MyTable
    where NumberValue <> @Number
    and CodeValue = @Code
    0
     
    LVL 8

    Accepted Solution

    by:
    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
     
    LVL 10

    Expert Comment

    by:imrancs
    OR

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


    Imran
    0
     
    LVL 10

    Expert Comment

    by:imrancs
    My suggestion is little modified version of one suggested by BulZeyE above.

    Imran
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     

    Author Comment

    by:BeginningWebDesign
    Thanks Everyone

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

    Caz
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    846 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

    8 Experts available now in Live!

    Get 1:1 Help Now