Filling two tables with a Foreign Key Relationship

Question

I need to create a star schema of table and apply on SSMS. To be simplier I focus on two tables:

One containing general informations about clients. Each client has a particular ID but can appear several times because he can be of multiple type.
One containing financial informations about them that are gathered every 3 months, each set of info about one client at a specific time will have an unique ID.

I set the client table as the parent table. I create it that way:

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info 1 nvarchar,
    Info 2 nvarchar,
    Info 3 nvarchar
)

I create the financial informations table this way:

CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CustomerID)
 ON DELETE CASCADE      
 ON UPDATE CASCADE     )

The thing is I don't know how to fill the costumerID in the FinInfo table without knowing it in the first hand.

score -1 · Answer 1 · answered Sep 28 '17 at 12:38

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info1 NVARCHAR(50),
    Info2 NVARCHAR(50),
    Info3 NVARCHAR(50)
)
GO
CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CostumerID)
 ON DELETE CASCADE      
 ON UPDATE CASCADE     
 )
 GO 

DECLARE @CustomerID INT
 INSERT INTO dbo.Customers
         ( Info1, Info2, Info3 )
 VALUES  ( N'info1', -- Info1 - nvarchar
           N'info2', -- Info2 - nvarchar
           N'info3'  -- Info3 - nvarchar
           )
SELECT @CustomerID = SCOPE_IDENTITY()
INSERT INTO dbo.FinInfo
        ( CustomerID, Date, Info1, Info2 )
VALUES  ( @CustomerID, -- CustomerID - int
          GETDATE(), -- Date - date
          7, -- Info1 - int
          8  -- Info2 - int
          )
GO

Filling two tables with a Foreign Key Relationship

1 Answers1