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.

This is typical DWH design used for reporting purposes, which means that data already exist in an OLTP DB. Generally using ETL process you fill up the data, or if you still want to add data directly you do that programmatically. — S4V1N, Sep 28 '17 at 11:13
Thanks for your answer. I know that I could, as you noted, used SSIS to create my connections. But for some reasons I don't have admin rights on the database, just a superuser. Therefore I need to find a workaround. (That's indeed DWH design) — endlessend2525, Sep 28 '17 at 11:48

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

-1

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

answered Sep 28 '17 at 12:38

Artashes Khachatryan

1,461
1
12
23

Perfect. I really understand the way It is built. But I get always the same value for CustomerID in the FinInfo table, is that normal ? – endlessend2525 Sep 28 '17 at 13:31
Whenever you insert new value in customers tables you must re-evaluate SCOPE_IDENTITY() function. It will return the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope. – Artashes Khachatryan Sep 28 '17 at 13:58
Alright I get the use of scope identity. The day I will need to get information on the two tables and i'll use on join on them, on what would I join if CustomerID always the same in the FinInfo table and different, per definition, in the Customers table ? – endlessend2525 Sep 28 '17 at 14:29
You must join by the customerIDs within both tables. – Artashes Khachatryan Sep 28 '17 at 17:05
I mean: In my Customers Table. I have always a different CustomerID (thanks to IDENTITY) but in the FinInfo table I only have one value for CustomerID (416). So if i join them on CustomerID I just get null for every customers but the last one with CustomerID = 416. – endlessend2525 Sep 29 '17 at 06:44

Filling two tables with a Foreign Key Relationship

1 Answers1