How can I create duplicate rows for each row in a table that does not contain a specific value?

Keep in mind, this IS for homework. I've been stuck on this problem for at least a week now.

I need to add a row to the table Vendors for each vendor (each has a VendorID, and VendorName) that does not have a VendorState value of CA.

I'm not quite grasping how to exclude rows with a specific value, but I suspect a sub-query is involved.

Any help is much appreciated.

edit-- here is the question word for word

Write an INSERT statement that adds a row to the VendorCopy table for each non-California vendor in the Vendors table. (This will result in duplicate vendors in the VendorCopy table.)

3 answers

  • answered 2018-05-16 05:46 sam28

    You just need this query:

    INSERT INTO table_vendors (VendorName, VendorState)
    SELECT VendorName, VendorState FROM table_vendors WHERE VendorState != 'CA'
    

    Keep in mind that if VendorId is a primary key or no duplicate index, you need this field to auto increment. Also, You need to specify all fields except VendorId because you can't duplicate the ID.

  • answered 2018-05-16 05:50 M.R.Sadeghi

    you can use cursor for retrieve each row data in select query.
    If I have understood correctly your question you can use this script for insert new row for specific condition:

    insert into Vendors
    SELECT 'new val' col1,'new val' col2, VendorState FROM Vendors
    where VendorState <> 'ca'
    


    ------------Edit---------------------
    if you want to create new table (copy of vendor) you can use this script:

    SELECT * into Vendors_Copy FROM Vendors
    WHERE VendorState <> 'ca'
    

  • answered 2018-05-16 05:52 Soham Dasgupta

    Something like this may be

    Insert into some_Other (col1, col2 ...)
    SELECT v.VendorName, o.State 
    FROM table_vendors v
    CROSS APPLY table_state o
    where v.vendor_id = o.vendorid and v.vendor_state != 'CA'