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
VendorName) that does not have a
VendorState value of
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.)
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.
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'
if you want to create new table (copy of vendor) you can use this script:
SELECT * into Vendors_Copy FROM Vendors WHERE VendorState <> 'ca'
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'