2

I have a table with several columns (and thousands of rows). I am trying to make a new set of rows work over several shops, so I need to copy a group of rows, change one column, and then reinsert them into my table (the column I'm changing is a key, so that's not a problem).

So far, I've gotten to this point:

INSERT INTO TableName( Column1, Column2, Column3, Column4, Column5, Column6)
(SELECT Column1, Column2, Column3, Column4, Column5, 'NewShopCode'
    FROM TableName
    WHERE Column5 = 'DistinguishingValue'

For each new shop, I need to do this for two different 'DistinguishingValue's, and I have about ten different 'NewShopCode's.

I have tried ('NewShopCode1', 'NewShopCode2') in place of the single literal insert above, as well as using VALUES before my SELECT statement. Neither of these work, returning a syntax error in each case.

Also, while, in this instance, it's not too much of a hassle to just do two separate queries for DV1 and 2, it would be nice if I could incorporate that into the one single query as well.

For clarification, I know I could just do the query 10 times with copy and paste and it would be pretty quick, and I totally would if it were just a database that I used around my office. Unfortunately, this is an update I need to script and send out to all my customers with the next version release of our software; ergo, single query = less phone calls complaining about how nobody's database works since it didn't update correctly.

Thanks in advance for your consideration.

Improve this question
1
  • what error are you getting? Commented Jun 3, 2013 at 22:10

1 Answer 1

Reset to default
2

Use a cross join. You didn't say what version of SQL you were using, so I'll low-ball it:

INSERT INTO TableName( Column1, Column2, Column3, Column4, Column5, Column6)
(SELECT Column1, Column2, Column3, Column4, Column5, a.[sc]
    FROM TableName as t
    cross join (

    select 'NewShopCode1' as [sc]
    union
    select 'NewShopCode2' as [sc]
    union
    select 'NewShopCode3' as [sc]

    ) as a
    WHERE Column5 = 'DistinguishingValue'
          and not exists (select 1 from TableName as t where t.Column1 = a.Column1 and t.Column2 = a.Column2)
)

Try the select by itself to see that it produces the correct results before inserting.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The select with the cross join returns the rows I need to add to my table, but now it's giving me a primary key error: Violation of PRIMARY KEY constraint 'PK_ElementDescriptions'. Cannot insert duplicate key in object 'dbo.ElementDescriptions'. The duplicate key value is (C401, JEV). The way the table is set up, there are two primary keys; one is the value I'm trying to change (the 'JEV' from my error) and the other is the elementID, C401. If I don't use the union, and just have the 'JEV' instead of a.[sc], it inserts just fine.
**Sorry, it turns out, that hardcoding it as 'JEV' returns the same error. It will only let me run the query with one new shop for each DistinguishingValue, then it gives me the primary key constraint.
A table can only have one PK, so you'll have to clarify what you mean by that (I suspect you mean that you have two columns in your PK). That said, it looks like at least one of the rows being generated by the SELECT statement already exists in your table. I've edited my answer for a possible mitigation. If you're on SQL 2008+, consider a merge statement instead.
I looked back through my code and figured out where the violation of PK constraint comes from. Since I'd already run the query to add a new shop code, my WHERE conditions were trying to add two versions of each row, one with the original shop and one with the shop I'd added. I guess I worded it incorrectly about the PK as well; the PK that distinguishes each row is the combination of a column called ElementID and the ShopCode column. SELECT ..... UNION works as needed, and I've selected your answer as correct. Thanks for your input.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.