Re: insert into test_b (select * from test_a) with different column order

On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen
<leif(at)solumslekt(dot)org> wrote:
> On Monday 29. March 2010 16.51.35 Ole Tange wrote:

>> I would like to do this:
>>
>>   insert into test_b (select * from test_a);
>
> Per the SQL standard, there's no inherent order between columns. That said,
> you'll usually get the columns in the order that they were created, but
> there's no guarantee for it.

And the create order in my case is (for all practical purposes) random.

> Actually, when you do a SELECT * FROM ... you
> make a totally unwarranted assumption that the columns will come out in any
> specific order.

I had hoped the INSERT would be intelligent enough to use the column
names and match on these.

> So, the answer to your question is to specify the columns
> explicitly in your query, as
>
> insert into test_b (select col_b, col_a from test_a);

This will not work for me as I do not know in advance what columns
exist in test_a or test_b. I only know they are called the same (and
have the same datatypes).

So is there a dynamic way in which I can generate the INSERT statement
given the name of the two tables?

Maybe something like listing all columns in test_b in the order that
test_b wants them and from this create the SELECT statement and
execute it?

/Ole