SQL Join question

[Peter Haworth]

Just looking at this again, I don't think the primary key column is an
issue if the goal is to put all tableB rows into tableA.  You'll have to
enumerate all the tableA and tableB columns omitting the primary key column
for each one:

INSERT INTO tableA (colA1,colA2,colA3.....,colAn) SELECT
colB1,ColB2,ColB3......,ColBn FROM tableB

... where the primary key column isn't named for either table.  That way a
new primary key value will be created automatically for each new row.  All
that, of course, assuming there is an INTEGER PRIMARY KEY column defined in
tableA

As an aside, it isn't necessary to include AUTOINCREMENT on the primary key
column to have it automatically calculated, at least in SQLite, all that's
needed is to define the column as INTEGER PRIMARY KEY.

With no AUTOINCREMENT, a newly inserted row will usually get the next
highest primary key value but there are exceptions.  For example, if the
highest possible primary key value has been reached, then any value made
available by the deletion of rows will be used.

With AUTOINCREMENT, every new row is guaranteed to have a primary key value
1 higher than the previous highest value so if you reach the highest
possible primary key value and then insert a new row, you'll get an error.

There's really isn't a practical difference since I think the maximum
possible highest key value is 2 to the power of 64.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sat, Jun 21, 2014 at 9:29 AM, Peter Haworth <pete at lcsql.com> wrote:

> Forget my earlier post, thought you were wanting to select not insert.
> John's INSERT...... SELECT is the way to do it.
>
> Pete
> lcSQL Software
> On Jun 21, 2014 7:57 AM, "Mark Smith" <Mark_Smith at cpe.umanitoba.ca> wrote:
>
>> I have two tables A and B, both with the same structure (about 50 columns
>> each). I would like to combine them into a single table (ie. rows from
>> Table
>> A followed by rows from Table B). Does anyone know of an SQL statement
>> that
>> will do that?
>>
>> Thanks
>>
>>
>>
>> --
>> View this message in context:
>> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>