Bulk Insert

Discovered one way to do a bulk update using XML in sql 2005, not sure if it is optimal, but it works :)




Schema for tblContractBranchCustomerLocations:

PK_ContractBranchCustomerLocationId int

,DaysOfWeek VARCHAR(50)

,RateDefinition VARCHAR(50)

,LastUpdateDate smalldatetime

,LastUpdateUser varchar(11)





DECLARE @xmlDoc XML

SET @xmlDoc = ''





UPDATE a

SET a.[DaysOfWeek] = Items.Item.value('@daysofweek','varchar(50)'),

a.[RateDefinition] = Items.Item.value('@ratedefinition','varchar(50)'),

a.[LastUpdateDate] = GETUTCDATE(),

a.[LastUpdateUser] = Items.Item.value('@audituser','varchar(11)')

FROM [tblContractBranchCustomerLocations] a

INNER JOIN @xmlDoc.nodes('/items/item') AS Items(Item) ON a.[PK_ContractBranchCustomerLocationId] = Items.Item.value('@id','int')