Perform insert for each row taken from a select?

Question

I have a number of records that I need to insert into multiple tables. Every other column will be a constant.

Poor pseudo code below - this is what I want to do:

create table #temp_buildings (     building_id varchar(20) ) insert into #temp_buildings (building_id) VALUES ('11070') insert into #temp_buildings (building_id) VALUES ('11071') insert into #temp_buildings (building_id) VALUES ('20570') insert into #temp_buildings (building_id) VALUES ('21570') insert into #temp_buildings (building_id) VALUES ('22570')  insert into property.portfolio_property_xref         ( portfolio_id ,           building_id ,           created_date ,           last_modified_date         ) values         (              34 ,             (                 select  building_id                 from    #temp_buildings             ) ,             getdate() ,             null         ) 

Intent: Perform an insert into property.portfolio_property_xref for each record on #temp_buildings

I think I could do this with a cursor - but believe this would be horribly slow. As this exercise will be repeatable in future I'd rather tackle this in a faster method but I'm unsure how. Any feedback would be appreciated!

Answer 1

INSERT INTO table1 ( column1 ) SELECT  col1 FROM    table2 

Like:

insert into property.portfolio_property_xref (      portfolio_id ,     building_id ,     created_date ,     last_modified_date ) select     34,     building_id,     getdate(),     null from     #temp_buildings