SQL union
-
I have a problem with the stored procedure that does the union on columns in from one table to another. I have table1 which has the following column names: A11, A12, A13, A21, A22, A23 and what the union does is to insert the records from table1 to table like this: insert into table2 as select A11 as a1, A12 as a2, a13 as a3 from table1 union select a21 as a1, a22 as a2 , a23 as a3 from table1 The procedure is working but what it does, if I have one record in table1 it duplicates the record after the union in table2. That is for one record in table, I get two records of the same record in table2. What might cause this? Please help as this gives me a lot of work in identifying the duplicates after running the procedure. Thanks:confused: phokojoe
-
I have a problem with the stored procedure that does the union on columns in from one table to another. I have table1 which has the following column names: A11, A12, A13, A21, A22, A23 and what the union does is to insert the records from table1 to table like this: insert into table2 as select A11 as a1, A12 as a2, a13 as a3 from table1 union select a21 as a1, a22 as a2 , a23 as a3 from table1 The procedure is working but what it does, if I have one record in table1 it duplicates the record after the union in table2. That is for one record in table, I get two records of the same record in table2. What might cause this? Please help as this gives me a lot of work in identifying the duplicates after running the procedure. Thanks:confused: phokojoe
You are unioning a different set of columns on the same table so it sees them as distinct. If you were unioning the same set of columns then it would remove the duplicate rows. If you do not want the same row being represented twice then you need to put in a WHERE statement to each to define what is permitted and what isn't. Alternatively you could perform two INSERT statements*. e.g.
INSERT INTO table2
SELECT [id], A11 AS A1, A12 AS A2, A13 AS A3
FROM table1INSERT INTO table2
SELECT [id], A21 AS A1, A22 AS A2, A23 AS A3
FROM table1
RIGHT OUTER JOIN table2 ON table1.[id] = table2.[id]
WHERE table2.[id] IS NULLHowever, you still need to define conditions as to what is acceptable and what isn't for these two inserts as the second INSERT will currently not add anything as each row will already be represented. Also, table2 needs a primary key that is shared with table1. I've assumed that to be a column called [id], you may need to change that. * DISCLAIMER: I've not tested this so it may not be 100% correct. I tend to get my right and left outer joins mixed up
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious