SQL: Insert data from another table in a table that contains foreign keys

Question

In a SQL DATABASE

I have a Table Users

 Id   Name   Age    AddressId
----+------+------+-----------

Where AddressId is a foreign key to a table names Addresses

The Addresses Table:

 Id   Country   State  City   ZipCode
----+---------+------+------+---------

This is a ONE-TO-ONE Relationship: Each User Has 1 address and each address has one user

I have a new table named NEWUsers

Id   Name  
----+------

It has only Id and Name.

What i want to do is this:

Write a script to insert all the records From the NEWUSers Table into the Users Table.

• I want The Age to be default 20 for all new users
• And for each new user inserted I need to create a new Address record for him
• the new Address record will have all it's values (country, city, state, zipcode) equal to "abcd" except the Id which will be used to set the foreign key AddressId for the new user)

How can I do that?

I tried the following:

INSERT INTO Users(Name, Age)
Values((SELECT Name FROM NewUsers),20)

But I don't know how to create a new Address record for each user inserted and specify the foreign key accordingly.

Thanks a lot for any help

Answer 1

One method would be with two queries, formed like the following:

INSERT INTO Addresses (Country, State, City, ZipCode)
SELECT 'abcd', 'abcd', 'abcd', 'abcd' FROM NewUsers

INSERT INTO Users (Name, Age, AddressId)
SELECT Name, 20, ?? FROM NewUsers

Edit: One simple way to link the users to the addresses would be to temporarily set the country to the username. Then you would have a link to determine the addressId. Once the users table is populated and linked up properly, you can set country back to the default value abcd:

insert addresses (country, state, city, zipcode)
select name, 'abcd', 'abcd', 'abcd' from newusers;

insert users (name, age, addressid)
select u.name, 20, a.id from newusers u
join addresses a on a.country = u.name;

update a
set a.country = 'abcd'
from addresses a join newusers u on a.country = u.name;

Demo: http://www.sqlfiddle.com/#!3/1f09b/8

There are more complex ways to do this if you want to guarantee transactional consistency if multiple inserts can happen simultaneously, or if you want to allow duplicate names, etc. But based on the example you've given and details so far, this method should work.

Answer 2

This is a little hacky, but does what you want in two statements - assuming no user is going to have the name 'abcd' or enter that for their country, and that you purge the NewUsers table after this operation:

INSERT dbo.Addresses(Country, State, City, ZipCode)
OUTPUT inserted.Country, 20, inserted.id
INTO dbo.Users
SELECT Name, 'abcd', 'abcd', 'abcd'
FROM dbo.NewUsers;

UPDATE a SET Country = 'abcd'
FROM dbo.Addresses AS a
INNER JOIN dbo.NewUsers AS nu
ON a.Country = nu.Name;

Answer 3

You Must Write Cursor For Insert In Users and Address Table With Forign key

DECLARE @AddressID INT,
        @ID INT,
        @Name NVARCHAR(50)

DECLARE UserCursor CURSOR FOR
SELECT ID, NAME
FROM NewUsers
OPEN UserCursor

FETCH NEXT FROM UserCursor INTO @ID, @Name
WHILE @@FETCH_STATUS =0 BEGIN
    INSERT INTO Addresses(Country, State, City, ZipCode)
    VALUES ('abcd', 'abcd', 'abcd', 'abcd')

    SET @AddressID = SCOPE_IDENTITY()

    INSERT INTO Users(Name, Age, AddressID)
    VALUES (@Name, 20, @AddressID)
    FETCH NEXT FROM UserCursor INTO @ID, @Name
END
CLOSE UserCursor
DEALLOCATE UserCursor

Answer 4

Assuming you can set the AddressId however you want...

INSERT Addresses
(
    Id,
    Country,
    State,
    City,
    ZipCode
)
SELECT
    Id,
    'abcd',
    'abcd',
    'abcd',
    'abcd'
FROM NEWUsers

INSERT Users
(
    Id,
    Name,
    Age,
    AddressId
)
SELECT
    Id,
    Name,
    20,
    Id
FROM NEWUsers

If AddressId is an identity column, you can disable the identity temporarily first. See SET IDENTITY_INSERT for info and examples.

Answer 5

Honestly, the answer is that you don't want to do what you think you want to do. If the users have one and only one address, as you stated, then you should have only one table (users) that contains the address fields. Modeling your data properly will make this problem go away intrinsically.