Stored Procedure union
-
I have a stored procedure which extracts data from one table and insert it into another table taking the union. For example: Table1 has 2 columns: az azz 1,23,4,56 2,34,5,67 and table2 has 4 columns a1,a2,a3,a4 after running the procedure table2 will have a1 a2 a3 a4 1 23 4 56 2 34 5 67 that is I have taken the union of az and azz stopping at the ",". As in above only 2 rows will be inserted in table2, but I get 4 rows, 2 of each record. How is this possible? Help! phokojoe
-
I have a stored procedure which extracts data from one table and insert it into another table taking the union. For example: Table1 has 2 columns: az azz 1,23,4,56 2,34,5,67 and table2 has 4 columns a1,a2,a3,a4 after running the procedure table2 will have a1 a2 a3 a4 1 23 4 56 2 34 5 67 that is I have taken the union of az and azz stopping at the ",". As in above only 2 rows will be inserted in table2, but I get 4 rows, 2 of each record. How is this possible? Help! phokojoe
Looking at your other post[^] I get the distinct impression you are hiding what you are really trying to achieve. You must help us to help you (to quote Dr. Cox from Scrubs). That means you must explain what you are actually trying to achieve rather than provide some absract concept that is missing some key elements.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Looking at your other post[^] I get the distinct impression you are hiding what you are really trying to achieve. You must help us to help you (to quote Dr. Cox from Scrubs). That means you must explain what you are actually trying to achieve rather than provide some absract concept that is missing some key elements.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Table1 has 3 columns and data is separetaed by "," like below. a | az | azz 20 | 1,23,4,56,..,9,10 | 9,..,1,32,54,7,88 Table2 has 8 columns;a a1 a2 a3 a4 a5 a6 a7 ---------------------------------------------- The code I write will look at the contents Table1 of 'az' and then read from left to right and when it finds the "," it takes the value to column a1 of Table2 and then continues to read and when it finds the second commna it takes the value to column a2 of Table2 and so on until it has reached "10" which is the last after the "," and puts it in column a7 of Table2 and then UNION it with the contents of column 'azz'. Table1 has 3 columns but 1 row and finally Table2 will have 8 columns and 2 rows. column 'a' is common to all the tables, so it will look like this after running the procedure. Table2: a a1 a2 a3 a4 a5 a6 a7 20 1 23 4 56 .. 9 10 20 9 .. 1 32 54 7 88 NB. column is acts the id It works like a traspose of Table1 to Table2. The problem is, it does not give me 2 rows a s I expected, but it gives 4 rows especially when some parts of 'az' or 'azz' are not filled, that is I put a dot where there is no entry. I used substring and patindex as shown below: INSERT table2(a,a1,a2,a3,a4,a5,a6,a7) SELECT a AS a,SUBSTRING(az,1,PATINDEX('%,%',az)- 1) AS a1, SUBSTRING(az,3,PATINDEX('%,%',az)+ 0) AS a2, SUBSTRING(az,6,PATINDEX('%,%',az)- 1) AS a3, SUBSTRING(az,8,PATINDEX('%,%',az)- 0) AS a4, SUBSTRING(az,11,PATINDEX('%,%',az)- 0) AS a5, SUBSTRING(az,14,PATINDEX('%,%',az)- 1) AS a6, SUBSTRING(az,16,PATINDEX('%,%',az)- 0) AS a7 FROM table1 UNION SELECT a AS a, SUBSTRING(aaz,1,PATINDEX('%,%',aaz)- 1) AS a1, SUBSTRING(aaz,3,PATINDEX('%,%',aaz)+ 0) AS a2, SUBSTRING(aaz,6,PATINDEX('%,%',aaz)- 1) AS a3, SUBSTRING(aaz,8,PATINDEX('%,%',aaz)- 0) AS a4, SUBSTRING(aaz,11,PATINDEX('%,%',aaz)- 0) AS a5, SUBSTRING(aaz,14,PATINDEX('%,%',aaz)- 1) AS a6, SUBSTRING(aaz,16,PATINDEX('%,%',aaz)- 0) AS a7 FROM table1 I hope it is clear now. why does it repeats the rows? thankx 4 the help. phokojoe
-
Table1 has 3 columns and data is separetaed by "," like below. a | az | azz 20 | 1,23,4,56,..,9,10 | 9,..,1,32,54,7,88 Table2 has 8 columns;a a1 a2 a3 a4 a5 a6 a7 ---------------------------------------------- The code I write will look at the contents Table1 of 'az' and then read from left to right and when it finds the "," it takes the value to column a1 of Table2 and then continues to read and when it finds the second commna it takes the value to column a2 of Table2 and so on until it has reached "10" which is the last after the "," and puts it in column a7 of Table2 and then UNION it with the contents of column 'azz'. Table1 has 3 columns but 1 row and finally Table2 will have 8 columns and 2 rows. column 'a' is common to all the tables, so it will look like this after running the procedure. Table2: a a1 a2 a3 a4 a5 a6 a7 20 1 23 4 56 .. 9 10 20 9 .. 1 32 54 7 88 NB. column is acts the id It works like a traspose of Table1 to Table2. The problem is, it does not give me 2 rows a s I expected, but it gives 4 rows especially when some parts of 'az' or 'azz' are not filled, that is I put a dot where there is no entry. I used substring and patindex as shown below: INSERT table2(a,a1,a2,a3,a4,a5,a6,a7) SELECT a AS a,SUBSTRING(az,1,PATINDEX('%,%',az)- 1) AS a1, SUBSTRING(az,3,PATINDEX('%,%',az)+ 0) AS a2, SUBSTRING(az,6,PATINDEX('%,%',az)- 1) AS a3, SUBSTRING(az,8,PATINDEX('%,%',az)- 0) AS a4, SUBSTRING(az,11,PATINDEX('%,%',az)- 0) AS a5, SUBSTRING(az,14,PATINDEX('%,%',az)- 1) AS a6, SUBSTRING(az,16,PATINDEX('%,%',az)- 0) AS a7 FROM table1 UNION SELECT a AS a, SUBSTRING(aaz,1,PATINDEX('%,%',aaz)- 1) AS a1, SUBSTRING(aaz,3,PATINDEX('%,%',aaz)+ 0) AS a2, SUBSTRING(aaz,6,PATINDEX('%,%',aaz)- 1) AS a3, SUBSTRING(aaz,8,PATINDEX('%,%',aaz)- 0) AS a4, SUBSTRING(aaz,11,PATINDEX('%,%',aaz)- 0) AS a5, SUBSTRING(aaz,14,PATINDEX('%,%',aaz)- 1) AS a6, SUBSTRING(aaz,16,PATINDEX('%,%',aaz)- 0) AS a7 FROM table1 I hope it is clear now. why does it repeats the rows? thankx 4 the help. phokojoe