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.