On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen
<leif@solumslekt.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