How to convert columns to rows

[ricky]

Hi,

I have some procedures with dynamic sql(generic procedure for different tables) to do the convert columns to rows but have performance issue. Is there a better way like a single SQL that can solve my problem, thanks!!!

Table A(total 20 columns)
Col1 Col2 Col3 Col4 ... Col20

Table B(total 10 columns)
Col1 Col2 Col3 Col4... Col10

My procedure like this(pseudo code)
---------------------------------
for loop(table) <-- Loop Table A & B
for loop(loop records in the table) <-- Records in Table A & Table B
for loop(loop columns in a table) <-- No. of columns in Table
insert into a new_table(ColName, ColValue, current timestamp)
end for
end for
end for

[Rabbit]

Use a UNION query.