My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables.
I have a script that will run on end users' computers as the user. (This is why I chose a stored procedure in the first place. Is there a better suggestion?). I need to insert data about the user's computer into the Computer table and data about the attached monitors into the Monitor table. What I would like to do is map the monitors to the computers in the mapping table. Is it possible to do all this in a stored procedure?
p.s I'm new to working with databases and I may be going about this all wrong.
EDIT: With the help of @Michael Green i was able to come up with the script below. However, I still can't get it working. Currently I'm reading up on how variables are set and used in SQL so I'm slowly getting it. Also, I'm using a Powershell script to do the inserting which I'm pretty sure I have on lock.
Because my database schema is so simple I'll just post it here. Note: All timestamps are set in the default values are not set manually.
-- My Table Parameter Value (TVP)
CREATE TYPE ComputerTableType AS TABLE
(Name nvarchar(15),
IPAddress nvarchar(15))
-- My Stored Procedure (SP)
CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@TVPComputer ComputerTableType READONLY
AS
DECLARE @TVPComputer TABLE (Computers int)
INSERT INTO dbo.Computers (Name, IPAddress)
OUTPUT INSERTED.Id
INTO @TVPComputer
VALUES (@TVPComputer);
INSERT INTO dbo.Monitors(Serial)
OUTPUT INSERTED.Id
INTO @Serial
VALUES (@Serial);
Aucun commentaire:
Enregistrer un commentaire