-2

I'm trying to create a stored procedure the inserts values into two tables tblAirport and tblCountry. tblAirport has a FK that references tblCountry and I want to insert the values to tblCountry using the values from the FK in tblAirport. I'm new to SQL and I think I have to use the SCOPE_IDENTITY() function to achieve this but I'm not sure how to implement it. These are my tables;

tblAirport 

PK INT IDENTITY(1,1),
geoLocation GEOGRAPHY,
ICAOCode VARCHAR(4),
IATACode VARCHAR(3),
strName VARCHAR(MAX),
strCity VARCHAR(MAX),
strCountry VARCHAR(MAX),
degLat DECIMAL(7,2),
minLat DECIMAL(7,2),
secLat DECIMAL(7,2),
Equator VARCHAR(1),
degLong DECIMAL(7,2),
minLong DECIMAL(7,2),
secLong DECIMAL(7,2),
Meridian VARCHAR(1),
strElevation VARCHAR(5),
FKCountry INT NULL

tblCountry 

PK INT IDENTITY(1,1),
strCountry VARCHAR(MAX)

This is what I have for my stored procedure

CREATE PROCEDURE [dbo].spInsertAirport
    @ICAOCode VARCHAR(4),
    @IATACode VARCHAR(3),
    @strName VARCHAR(MAX),
    @strCity VARCHAR(MAX),
    @strCountry VARCHAR(MAX),
    @degLat DECIMAL(7,2),
    @minLat DECIMAL(7,2),
    @secLat DECIMAL(7,2),
    @Equator VARCHAR(1),
    @degLong DECIMAL(7,2),
    @minLong DECIMAL(7,2),
    @secLong DECIMAL(7,2),
    @Meridian VARCHAR(1),
    @strElevation VARCHAR(5),
    @FKCountry INT
AS  
BEGIN TRANSACTION
BEGIN TRY
    SET NOCOUNT ON  
    SET ANSI_WARNINGS OFF  

    INSERT INTO tblAirport (ICAOCode, IATACode, strName, strCity, strCountry, 
                            degLat, minLat, secLat, Equator, 
                            degLong, minLong, secLong, Meridian, strElevation)
    VALUES (@IATACode, @strName, @strCity, @strCountry, 
            @degLat, @minLat, @secLat, @Equator, 
            @degLong, @minLong, @secLong, @Meridian, @strElevation)

I don't know how to insert the countries into the tblCountry table; any help is awesome

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Tat Scagliotti
  • 69
  • 2
  • 2
  • 4
  • Why does your `tblAirport` table have `strCountry VARCHAR(MAX)`? That should come from your `tblCountry` table. Also, consider getting rid of the `tbl` prefixes as they are useless. – Yuck Dec 18 '17 at 20:45
  • they are all specs for an assignment – Tat Scagliotti Dec 18 '17 at 20:50

1 Answers1

0

You don't need scope_identity.

Check your tblCountry to see if there is already a row for @strCountry. If there isn't, then insert a new row for it.

Then update tblAirport, setting FKCountry to the value of PK for the row that matches on strCountry.

You could also do this during the insert instead of using an update, but it sounds like you are at a very beginner level, so maybe just take this easier approach for this assignment.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I'm not sure what you mean in tblCountry I do have a column for strCountry, what I'm trying to do is to populate this table using a stored procedure. I have the insert to populate tblAirport in the stored procedure and I want to make an insert for tblCountry but I want the country data from tblAirport to use in tblCountry – Tat Scagliotti Dec 18 '17 at 22:51
  • What I mean is at the beginning of your procedure, do a SELECT on tblCountry to see if there is already a PK for the parameter @strCountry. That way you know if you have to insert a new one or not. You are looking at it backwards, by the way. You get the country data from tblCountry to populate FK in in tblAirport. NOT the other way around. – Tab Alleman Dec 19 '17 at 19:01