4

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

  • Does the DataSource have a UNIQUE constraint or a column (or columns) that can be used to uniquely identify rows? – ypercubeᵀᴹ Jun 9 '16 at 9:31
  • Yes, DataSource has a unique number (user_id) – TryNCode Jun 9 '16 at 9:35
  • And that user_id does not go to the Users table, too? – ypercubeᵀᴹ Jun 9 '16 at 9:38
  • It does but it's not used as the primary key, the PK is an auto-incrementing integer – TryNCode Jun 9 '16 at 9:41
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.