SQL Server – INSERT INTO Inserting Multiple Times

duplicationimportsql servert-sql

I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called 'Address' and 'User'. What I'd like to do is take the Address column for a user in DataSource and add it to the Address table. I would then like to take the First Name, Surname, and AddressID (created upon Address insertion) and add it to the User table. (Basically, split the data from DataSource into two tables and have the AddressID link between the two of them via the auto-generated AddressID from the Address table).

I've got the code below but it's not behaving as expected. The User table is being filled in a loop so every user is inserted the number of rows (e.g. if there are two people in the DataSource table, each user is inserted twice and given every address ID each time)

DECLARE @ids TABLE (address_id INT);

INSERT INTO dbo.Address(address) 
OUTPUT INSERTED.address_id INTO @ids
SELECT [Address]    
FROM dbo.[DataSource]


SET IDENTITY_INSERT dbo.[Users] OFF      

INSERT INTO dbo.[Users]
        ( 
          first_name,
          surname ,
          address_id            
        )

SELECT  first_name, surname,i.address_id,        
FROM dbo.[DataSource], @ids i

How can I essentially do this sequentially so that the correct address is assigned once to the user? This is not a time sensitive operation so performance is not important but the integrity of the data is crucial.

Thanks

Best Answer

You are doing a cartesian product here:

...
FROM dbo.[DataSource], @ids i

you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it:

INSERT INTO dbo.[Users]
        ( 
          first_name,
          surname ,
          address_id            
        )

SELECT  first_name, surname, Address.address_id,        
FROM dbo.[DataSource]
INNER JOIN dbo.Address ON Address.address = DataSource.Address

A further consideration; this will work well as long as you don't have two users with the same address, or if it is desirable that two users with the same address have the same address_id.

Related Question