CODEWITHSUNDEEP
sqlinsert
DrStrangeLove
DrStrangeLove

Reputation: 11567

How do i insert new record in this table?

I'm sorry for this noob question.

There're 2 tables: the smaller one is derived from bigger one. How do i insert new record into bigger table??

INSERT INTO people (lname, fname, city, age, salary) VALUES (' Doe','John','Paris', '25','1000$' );

but bigger table contains city as number. How should i insert 'Paris'?? Should i know its number beforehand?? But 'Paris' isn't in Cities (smaller) table!! How do records get inserted in bigger (people) table??

Upvotes: 0

Views: 137

Answers (3)

rabudde
rabudde

Reputation: 7722

EDIT: adopted solution for existing entries in city table. as I wrote in comments, transactions could be ommited (for example when running as stored procedure)

BEGIN TRANSACTION
        DECLARE @city_id INT
        SELECT @city_id=id FROM city WHERE name='Paris'
        IF @city_id IS NULL
        BEGIN
            INSERT INTO city (name) VALUES ('Paris')
            SET @city_id=@@IDENTITY
        END
        INSERT INTO people (lname, fname, city, age, salary) VALUES ('Doe','John',@city_id, '25','1000$' );
END TRANSACTION

Upvotes: 0

JNK
JNK

Reputation: 65217

EDIT:

Added IF block to check for Paris.

IF NOT EXISTS (SELECT 1 FROM City WHERE City = 'Paris')
Insert INTO City (City) VALUES ('Paris')

DECLARE @Cid int = (SELECT CityID FROM City WHERE City = 'Paris')

INSERT INTO people (lname, fname, city, age, salary) 
VALUES (' Doe','John', @cid, '25','1000$' )

I made an assumption about the structure of the city table obviously.

You could also parameterize this with a @city variable and sub that for 'Paris' everywhere in the code.

Upvotes: 1

Mark Watts
Mark Watts

Reputation: 764

In the insert to the people table, the value of the city column should be the number of the city you want the person to be linked to - so in your quoted example replace 'Paris' with the city number for Paris.

If you want to create a new person record with a city that does not yet exist in the cities table, you'll need to do an insert into the cities table first, get the number of the created city and then use that in your insert to the people table.

Upvotes: 0

Related Questions