CODEWITHSUNDEEP
sql-server
Richard
Richard

Reputation: 47

Inserting into a joined view SQL Server

This is a question more about design than about solving a problem.

I created three tables as such

CREATE TABLE [CapInvUser](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](150) NOT NULL,
    [AreaId] [int] NULL,
    [Account] [varchar](150) NULL,
    [mail] [varchar](150) NULL,
    [UserLevelId] [int] NOT NULL
)

CREATE TABLE [CapInvUserLevel](
    [UserLevelId] [int] IDENTITY(1,1) NOT NULL,
    [Level] [varchar](50) NOT NULL
)

CREATE TABLE [CapInvUserRegistry](
    [UserRegistryId] [int] IDENTITY(1,1) NOT NULL,
    [UserLevelId] int NOT NULL,
    [DateRegistry] DATE NOT NULL,
    [RegistryStatus] VARCHAR(50) NOT NULL,

)

With a view that shows all the data on the first table with "AreaId" being parsed as the varchar identifier of that table, the UserLevel being parsed as the varchar value of that table, and a join of the registry status of the last one.

Right now when I want to register a new user, I insert into all three tables using separate queries, but I feel like I should have a way to insert into all of them at the same time.

I thought about using a stored procedure to insert, but I still don't know if that would be apropiate. My question is

--This are just representations of the tables, not the real ones. -- I'm still learning how to work with SQL Server properly.

Thank you for your answers and/or guidance.

Upvotes: 0

Views: 61

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

The most common way of doing this, in my experience, is to write a stored procedure that does all three inserts in the necessary order to create the FK relationships.

This would be my unequivocal recommendation.

Upvotes: 1

Related Questions