对选定的每一行执行插入操作？

我有许多记录需要插入到多个表中。每隔一列都是常数。

可怜的伪代码-这是我想要做的:

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
)

意图: 对 # temp _ building 上的每个记录执行一个对 properties. folio _ property _ xref 的插入

我认为我可以做到这一点与光标-但相信这将是非常缓慢。因为这个练习在将来是可重复的，我宁愿用一种更快的方法来解决这个问题，但我不确定如何解决。如有任何反馈将不胜感激！

89416 次浏览
小开
最佳答案 
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
小开

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
小开

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
小开

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.

小开

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


提交答案