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.
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
If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.
To retrieve data from both table associated with foreign key i.e(common column) you have to join both the tables. if you matching data from both table then use INNER JOIN. >
To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column.
Of course you can add a foreign key when there is data... assuming you don't have any values that violate the new constraint.
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.
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With