0

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.

2
  • 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, 2017 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) Sep 28, 2017 at 11:48

1 Answer 1

-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
5
  • 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 ? Sep 28, 2017 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. Sep 28, 2017 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 ? Sep 28, 2017 at 14:29
  • You must join by the customerIDs within both tables. Sep 28, 2017 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. Sep 29, 2017 at 6:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.