INSERT SQL records from one database to a second (where 2nd has an additional column)

I’d like to insert select records from Table A –> Table B (in this example case, different "databases" == different tables to not worry about ATTACH), where Table A has less columns than Table B. The additional B_Table column (col3) should also be populated.

I’ve tried this sequence in raw-SQL (through SQLAlch.):

1.) INSERTing A_Table into Table B using an engine.connect().execute(text)

text("INSERT INTO B_Table (col1, col2) SELECT col1, col2 FROM A_Table")

2.) UPDATEing B_Table w/ col3 info with an engine.connect()ion (all newly inserted records are populated/updated w/ the same identifier, NewInfo)

text("UPDATE B_Table SET col3 = NewInfo WHERE B_Table.ID >= %s" % (starting_ID#_of_INSERT'd_records))

More efficient alternative?

But this is incredibly inefficient. It takes 4x longer to UPDATE a single column than to INSERT. This seems like it should be a fraction of the INSERT time. I’d like to reduce the total time to ~just the insertion time.

What’s a better way to copy data from one table to another w/out INSERTing followed by an UPDATE? I was considering a:

1.) SQLAlchemy session.query(A_Table), but wasn’t sure how to then edit that object (for col3) and then insert that updated object w/out loading all the A_Table queried info into RAM (which I understand raw-SQL’s INSERT does not do).

>Solution :

You can use 'NewInfo' as a string literal in the SELECT statement:

INSERT INTO B_Table (col1, col2, col3) 
SELECT col1, col2, 'NewInfo' 
FROM A_Table;