CODEWITHSUNDEEP
sqlsql-server-2008t-sqlinsert
Michael A
Michael A

Reputation: 9900

Perform insert for each row taken from a select?

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!

Upvotes: 91

Views: 106990

Answers (5)

Jon Milliken
Jon Milliken

Reputation: 121

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

Upvotes: 2

Mark Kremers
Mark Kremers

Reputation: 1789

You are saying that you can do it with a cursor. As the other answers show you, you wont have to do it. SQL Server is a set based RDMS, it is more capable of processing a set of data, then of processing single lines.

Upvotes: 0

Taryn
Taryn

Reputation: 247610

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

Upvotes: 14

codingbiz
codingbiz

Reputation: 26376

Try this

   insert into property.portfolio_property_xref
   ( 
      portfolio_id ,
      building_id ,
      created_date ,
      last_modified_date
   )
   Select
      34,
      building_id,
      GETDATE(),
      NULL
   From #temp_buildings

Upvotes: -1

Justin Skiles
Justin Skiles

Reputation: 9503

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

Upvotes: 174

Related Questions