none
Insert statement using multiple values without repeating each line

    Question

  • I'm trying to create a Insert statement where it checks to see if a row exists and if not, then the row gets added in but I have one column that changes in each insert line.

    So for example,

    DECLARE LName char(12)

    SET Lname='Smith'

    If not exists (select * from myTable where Firstname = 'John' and Lastname = Lname)

    Insert into myTable (Firstname, Lastname)

    Values ('John', LName)

    Now I want LName to hold the values 'Smith','Robertson','Snyder' and loop through this statement for as many values I have under LName (in this case 3). From my research, I found out that the only way you can have a variable holding multiple values is through a table. Problem though is that you can't use a column name in an insert statement.

    The only way I can do this now is to copy and paste all the lines from SET to Values and reset the Lname each time which makes the query that much longer. Is there an easier way?

    Friday, July 04, 2014 6:56 PM

Answers

  • Assuming LName values come from a table you can do this

    Insert into myTable (Firstname, Lastname)
    select 'John', LName
    from Yoursourcetable t
    where not exists (select 1
    from myTable
    where Firstname = 'John'
    and Lastname = t.LName
    )

     in case you dont have it in existing table, you can create one on the fly

    Insert into myTable (Firstname, Lastname)
    select 'John', LName
    from (VALUES ('Smith'),('Next name value'),...)t(Lname)
    where not exists (select 1
    from myTable
    where Firstname = 'John'
    and Lastname = t.LName
    )

    and you dont need the IF EXISTS check also in above cases


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, July 04, 2014 7:37 PM

All replies

  • DECLARE @names TABLE (firstName VARCHAR(50), lastName VARCHAR(50))
    INSERT INTO @names (firstName, lastName)
    VALUES 
    ('John', 'Smith'),('John', 'Robertson'),('John', 'Snyder')
    
    DECLARE @targetTable table (ID int IDENTITY, firstName varchar(50), lastName varchar(50))
    
    
    INSERT INTO @targetTable
            ( firstName, lastName )
    SELECT firstName, lastName
      FROM @names n
      WHERE NOT EXISTS (SELECT * FROM @targetTable t WHERE n.lastName = t.lastName)
    
    SELECT * FROM @targetTable
    
    INSERT INTO @targetTable
            ( firstName, lastName )
    SELECT firstName, lastName
      FROM @names n
      WHERE NOT EXISTS (SELECT * FROM @targetTable t WHERE n.lastName = t.lastName)

    This demonstrates how to do what you need.

    The insert runs twice, but the records are only inserted once.

    Friday, July 04, 2014 7:36 PM
  • Assuming LName values come from a table you can do this

    Insert into myTable (Firstname, Lastname)
    select 'John', LName
    from Yoursourcetable t
    where not exists (select 1
    from myTable
    where Firstname = 'John'
    and Lastname = t.LName
    )

     in case you dont have it in existing table, you can create one on the fly

    Insert into myTable (Firstname, Lastname)
    select 'John', LName
    from (VALUES ('Smith'),('Next name value'),...)t(Lname)
    where not exists (select 1
    from myTable
    where Firstname = 'John'
    and Lastname = t.LName
    )

    and you dont need the IF EXISTS check also in above cases


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, July 04, 2014 7:37 PM
  • INSERT INTO Smith_Family (first_name, last_name)
    SELECT X.first_name, 'Smith' AS last_name
       FROM (VALUES ('A', 'B', 'C'))
             AS X (last_name);

    SQL is declarative, so we do not use local variables or loops. Your mind set is wrong.



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 05, 2014 1:00 AM