August 3, 2010 at 11:48 am
You should look at the OUTPUT clause. You can use that to capture the ID's generated for all the addresses you just inserted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2010 at 2:57 pm
Hi,
Thanks for your reply. I tired using OUTPUT clause but not sure how to get the DealerId. Below is my query.
CREATE TABLE #tAddress
(
AddressId INT IDENTITY(1,1),
AddressId varchar(255),
City varchar(255),
StateId int,
PostalCode varchar(50),
CountryId int
)
DECLARE @Dealer_Addresses TABLE (DealerId INT, AddressId)
INSERT INTO #t
(
AddressId,
City,
StateId,
PostalCode,
CountryId
)
OUTPUT t.DealerId, Inserted.AddressId INTO @Dealer_Addresses
SELECT TOP(5)
Addr,
City,
s.StateId,
PostalCode,
s.CountryId
FROMtmpDealer_Imports t
INNER JOIN Dealers d ON d.DealerId = t.DealerId
INNER JOIN States s ON s.StateAbbrev = t.ST
SELECT*
FROM@Dealer_Addresses
SELECT*
FROM#t
DROP TABLE #t
How would I get the DealerId so that gets inserted into Dealer_Addresses table for the new Addresses?
August 4, 2010 at 5:30 am
DealerID is being supplied from the original file, right? That's where you get it. Presumably you can join back to the original file with the address information to get the combination of the new AddressId and the DealerId for inserting into the other table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2010 at 8:05 am
Hi Grant,
Will it be good to join back to the file using Address info? Sometime, the address could be the same for 2 dealers.
I tried adding a variable to hold the dealerid but it seems like you can't add a variable in an insert statement..
INSERT INTO table
(
filed1,
field2,
@variable
)
August 4, 2010 at 8:30 am
You can use a variable on an insert, but there has to be a column to insert it into. You can't just throw it in as a placeholder.
And yes, it's OK that multiple dealers have the same address. That's what data normalization is all about. You should only get a single address record and then that record is joined to more than one dealer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply