Menu
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!
716
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
