Insert a part of a table into another table

Question

I have table A and table B, same schemas.

I want to insert certain rows from table A into table B. For example, insert into table B all rows from table A with column 'abc' > 10.

Couldn't figure out how to do it

Answer 1

You can use the following notation:

BEGIN TRAN
INSERT INTO ExistingTable (Col1, Col2...)
SELECT Something1, Something2... FROM Table1 WHERE ...
--ROLLBACK/COMMIT

Answer 2

Something like this

INSERT INTO B (supplier_id, supplier_name)
SELECT supplier_id, supplier_name FROM A
WHERE abc > 10;

Make sense?

Answer 3

At first blush, I'd say something like:

Insert Into B
(Select * from A
Where abc > 10)

Answer 4

I like to do it this way :

create table temp_B
as
select * from A
where abc > 10
union
select * from B;
drop table B;
rename temp_B to B;

If you have to insert millions+ rows, you could win several hours. Don't forget to recreate index & recompile objects.