Perform insert for each row taken from a select?

Solution 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

Solution 2:

You will want to use INSERT INTO SELECT FROM (See SQL Fiddle with Demo)

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

Solution 3:

Kind of random, but I feel this may be useful to anyone who comes here to this question. Sometimes, i use Microsoft Excel VBA to generate portions of SQL Statements like listed above. I find this very useful when I am in situations where i am doing table construction and data transformation to set up a new job. this is a really simple example. It created a link between 2 separate unrelated systems. Then the link allowed me to build a new table in a warehouse environment that tied 3 unrelated systems together. Anyway, it allowed me to create > 5000 lines of SQL (for onetime use - and a small part of a much larger ETL task) in seconds.

Option Explicit

Dim arow As Integer
Dim acol As Integer
Dim lrow As Integer
Dim IsCellEmpty As String
Dim CustNo As Integer
Dim SkuLevel As Integer


Sub SkuLevelUpdate()

'find end ouf input file
arow = 1
acol = 1

Do
    IsCellEmpty = Cells(arow, acol).Value
    arow = arow + 1
Loop Until IsCellEmpty = ""

lrow = arow - 1

'Write SQL
arow = 2
acol = 5

Do
    CustNo = Cells(arow, 1)
    SkuLevel = Cells(arow, 4)
    Cells(arow, acol) = "INSERT INTO dbo.#TempSkuLevelRelationships (CustNo, SkuLevel) VALUES (" & CustNo & ", " & SkuLevel & ");"
    arow = arow + 1
Loop Until arow = lrow

End Sub

Yes, I know all about SQL injection, etc. I create the spreadsheet(s), I copy/paste the data into larger SQL code for new construction, table modifications, and the like when the data does not currently reside in a SQL table