|
Subject:
|
Inserting data in multiple tables
|
|
Posted By:
|
rohits
|
Post Date:
|
8/28/2006 6:38:02 AM
|
Hello !
I have got following tables:
tbZip
(
Zip [primary key],
City ,
StateId [Foreign key],
CountryId [Foreign Key]
)
tbState
(
StateId [pk],
StateNm,
CountryId [fk]
)
tbCountry
(
CountryId [pk],
CountryNm ,
Continent
)
If I want to insert data in tbZip, I am using either of the statements:
Insert into tbZip(Zip, City, StateId, CountryId)
Values ('12345', 'xyz', 1,2)
--OR--
Insert into tbZip(Zip, City, StateId, CountryId)
select '12345' as Zip, 'xyz' as City, 1 as StateId, 2 as CountryId
Both of these are working.
But for this.. each time I have to look for values of StateId and CountryId in the respective tables.
Can it be possible that.. I can write a single Insert statement to insert values directly from tbState and tbCountry tables.. Something like:
Insert into tbZip(Zip, City, StateId, CountryId)
Values ('12345', 'xyz', (select StateId from tbState where StateNm='MH'), select Country Id from tbCountry where CountryNm='IN'))
The purpose is.. if the values of StateId and CountryId are dependent upon tbState and tbCountry then..the values should validate from the respective tables for specific user selected values.
Please Help.
Thanks
|
|
Reply By:
|
veggivore
|
Reply Date:
|
8/29/2006 11:32:07 PM
|
Wrong forum, but here goes:
This will not write any rows if your state or country is not in the related tables:
INSERT INTO tbZip(Zip, City, StateId, CountryId)
SELECT '12345', 'xyz', tbState.StateId, tbCountry.CountryId
FROM tbState, tbCountry
WHERE StateNm='MH'
AND CountryNm='IN'
If your referential integrity rules allow nulls then:
INSERT INTO tbZip(Zip, City, StateId, CountryId)
SELECT '12345', 'xyz',
(SELECT StateId FROM tbState WHERE StateNm='MH'),
(SELECT CountryId FROM tbCountry WHERE CountryNm='IN')
|
|
