August 3, 2010 at 8:58 am
Hi there,
I have an Address table with AddressId and other Address fields.
Then there is Dealer_Addresses table with DealerId and AddressId.
I am getting a file with DealerId and Address Information that I need to add as new if a dealer does not have Address info in our db.
Until now, I was using CURSOR whenever I had to add Address, get the AddressId and then insert that to Dealer_Addresses table. Since the file I am getting is huge and using CURSOR is taking a lot of time.
Is there a way to do this without using a CURSOR?
If I do a bulk insert, i don't know how would I get the AddressId and the DealerId so I can add that entry to the Dealer_Addresses table. Any advise is greatly appreciated.
Thanks
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply