How can insert a subquery into the table using Declare-CodePudding

I am trying to code and run a full table so it can run sucessfully without error. I am also making the sql table, by using Address and Person Table.

Here is the error

Msg 102, Level 15, State 1, Line 159 Incorrect syntax near '='.

/*
Foundation Lab
Creating Objects in SQL
Purpose: Create objects in SQL and set all the properties to achieve a robust well performing database: tables, views, sequences

Each comment describes a task
Write  SQL statement(s)  for the task under the comment

The script must be able to run repeatedly (which is why you will need to drop tables so that you can recreate them)
The SQL script meu be able to run completely and correctly on another database.
*/

/*
Tidy up any previous runs by dropping (deleting) any objects that this script creates.  
Use the IF EXISTS clause for brevity.
*/
/*
Drop an existing table "Address"
Drop an existing View "AddressView"
Drop an existing table "Person"
Drop an existing view "Person"
*/
DROP TABLE IF EXISTS [dbo].[Person]
DROP TABLE IF EXISTS [dbo].[Address]
DROP VIEW  IF EXISTS[dbo].[PersonView]
DROP VIEW  IF EXISTS [dbo].[AddressView]
GO

/*
Create an Address  table with columns: 
    AddressKey INT
    HouseNumber INT
    StreetName VARCHAR(100)
    PostCode VARCHAR(8)
Set the AddressKey column as the primary key
Set the AddressKey column as an identity column
All columns must be not NULL
*/

/*
Add three  rows to the table for the addresses
with the following values for HouseNumber, StreetName and PostCode
*/
CREATE TABLE Address
(
    AddressKey INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    HouseNumber INT NOT NULL,
    StreetName VARCHAR(100) NOT NULL,
    PostCode VARCHAR(8) NOT NULL
);

/*
Add three  rows to the table for the addresses
with the following values for HouseNumber, StreetName and PostCode
    32, 'Acacia Avenue', 'SL1 1AA'
    52, 'Cambray Road', 'SW12 9ES'
    10, 'Downing Street', 'SW1A 2AA'
Use a single statement, not three statements
*/

INSERT INTO Address
    (
    HouseNumber,
    StreetName,
    PostCode
    )
VALUES
    (10, 'Downing Street', 'SW1A 2AA'),
    (52, 'Cambray Road', 'SW12 9ES'),
    (32, 'Acacia Avenue', 'SL1 1AA');


-- Check that the data in the Address tables is as expected
SELECT *
FROM Address;


/*
Create a view, named AddressView,  that has HouseNumber and PostCode columns only
*/
GO
CREATE VIEW AddressView
AS
    SELECT HouseNumber,
        PostCode
    FROM Address;
GO

-- Check the view works as expected.
SELECT *
FROM AddressView;

/*
Create a Person table with columns: 
    PersonKey INT 
    AddressKey  INT 
    FirstName VARCHAR(100),
    LastName VARCHAR(100)
    DateOfBirth DATE  
Set the PersonKey column as the primary key.
Set the PersonKey column as an identity column
All columns must be NOT NULL.
*/

CREATE TABLE Person
(
    Personkey INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AddressKey INT NOT NULL,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    DateofBirth DATE NOT NULL
);




-- Check that the rows are now in the Person table
SELECT *
FROM Person;

/*
Create a foreign key relationship 
so that AddressKey in the Person table references the AddressKey column in the Address table. 
*/
--ALTER TABLE Person ADD FOREIGN KEY (AddressKey) REFERENCES Address (AddressKey);
ALTER TABLE Person
ADD
    FOREIGN KEY (AddressKey) REFERENCES Address (AddressKey);


/*
Add two sample rows to the table
* Boris Johnson in Downing Street (use variables)
* Theresa May in Cambray Road (use a SQL sub query)
*/
--'1959-01-19')
DECLARE @AddressKeyPM INT;
SELECT @AddressKeyPM = a.AddressKey FROM Address a WHERE a.PostCode = 'SW1A 2AA' AND a.HouseNumber = 10;
SELECT @AddressKeyPM

INSERT INTO Person
    (
    AddressKey,
    FirstName,
    LastName,
    DateofBirth
    )
VALUES
    (@AddressKeyPM, 'Boris', 'Johnson', '1964-06-19');

INSERT INTO Person
    (
    AddressKey,
    FirstName,
    LastName,
    DateofBirth
    )
VALUES
    (@AddressKeyPM = a.AddressKey FROM AddressKey a WHERE a.Postcode = 'SL1 1AA' AND a.HouseNumber = 32, 'Theresa', 'May', '1956-10-01');



-- Check that the Person table now has these two rows of data.
SELECT *
FROM Person;




/*
Show that the foreign key constraint is active
Try insert a row with a value for Addresskey that is not in the Address table
e.g. Kier Starmer, born on '1963-01-19', with AddressKey 12345
Note the error message
*/


/*INSERT INTO Person
    (AddressKey, FirstName, LastName, DateofBirth)
VALUES
    ('12345', 'Kier', 'Starmer', '1963-01-19');
*/
-- It won't work because it needed updating with the main table with is the address table. --


-- Create a PersonView view that the FirstName and LastName (but not the DateOfBirth)
GO
CREATE VIEW PersonView
AS
    SELECT FirstName,
        LastName
    FROM Person;
GO


/*
Opens the person view before altering
*/
SELECT *
FROM PersonView;
GO

-- Extend the view to include the House Number and PostCode from the Address table
SELECT * FROM Address a INNER JOIN Person p on p.AddressKey = a.AddressKey
GO

-- Alter the view to show only Boris and Theresa First, Last, HouseNumber and Postcode
Alter VIEW dbo.PersonView
AS
SELECT  p.FirstName, p.LastName, a.HouseNumber, a.PostCode
FROM Person p INNER JOIN Address a on p.AddressKey = a.AddressKey
 


-- Check that the view is working correctly
GO
SELECT *
FROM PersonView;

So If you would like to teach me how to correct and fix the coding, then that would be great!

CodePudding user response：

If you want to write a sub query then you can try this

INSERT INTO Person
    (
    AddressKey,
    FirstName,
    LastName,
    DateofBirth
    )
SELECT a.AddressKey, 'Theresa', 'May', '1956-10-01' FROM Address a WHERE a.PostCode = 'SL1 1AA' AND a.HouseNumber = 32;

CodePudding user response：

Try this:

INSERT INTO Person
    SELECT
        AddressKey  =   (SELECT AddressKey FROM Address WHERE Postcode = 'SW1A 2AA' AND HouseNumber = 10)
    ,   FirstName   =   'Boris'
    ,   LastName    =   'Johnson'
    ,   DateofBirth =   '1964-06-19'
;
INSERT INTO Person
    SELECT
        AddressKey  =   (SELECT AddressKey FROM Address WHERE Postcode = 'SL1 1AA' AND HouseNumber = 32)
    ,   FirstName   =   'Theresa'
    ,   LastName    =   'May'
    ,   DateofBirth =   '1956-10-01'
;