June 19, 2008 at 9:37 am
I'll try to explain this as best I can. I'm no TSQL expert by any means, so maybe this is very simple, or maybe it isn't quite possible the way it's currently designed.
I have 3 tables, pertaining to a defined business process (Server Build).
One table, dbo.Server_Master, will have one row inserted for each new server that is purchased (ServerName, SerialNumber, etc).
The second table, dbo.Server_Steps, contains 40 static rows, detailing each step in a pre-defined build process (Install NIC, Install OS, etc).
The third table, dbo.Server_Status needs to be populated with all of the 40 static rows from dbo.ServerSteps, with some of the info from dbo.Server_Master appended to the beginning of the row.
My assumption is some sort of join, but I'm not sure, since the Server_Steps table doesn't have anything to join on.
For example:
dbo.Server_Master
ServerID (identity) ServerName SerialNum OpSys
1 ServerABC 123456789 UNIX
When that row gets entered into dbo.Server_Master (info will eventually be coming from an InfoPath form), I already have a trigger set up to fire off a stored procedure. Right now, I have the stored procedure inserting a row into dbo.Server_Status like so:
dbo.Server_Status
ServerID (identity) ServerName SerialNum OpSys StepID StepDescription
1 ServerABC 123456789 UNIX NULL NULL
The contents of dbo.Server_Steps is something like this:
dbo.Server_Steps
StepID StepDescription
1 Install NIC
2 Install Operating System
3 Add server to domain
4 Assign IP
. .
. .
40 Setup Complete
So, what I need to get to is where dbo.Server_Status essentially has 40 rows for each new server being built, like so:
dbo.Server_Status
ServerID (identity) ServerName SerialNum OpSys StepID StepDescription
1 ServerABC 123456789 UNIX 1 Install NIC
1 ServerABC 123456789 UNIX 2 Install Operating System
1 ServerABC 123456789 UNIX 3 Add server to domain
1 ServerABC 123456789 UNIX 4 Assign IP
and so forth
Perhaps there is a much better way to go about doing this, I can't make any claims on that... this isn't my project, but I'm trying to help someone out who knows no TSQL at all, and I'm not much further ahead myself. I've only given a very scaled down outline of the table design here, but the table design is not set in stone at all... this individual created the tables without any concrete database design in mind... everything was sort of pieced together as the need arose. If posting the table structures and my current procedure and trigger is necessary, I will do so.
"Got no time for the jibba jabba!"
-B.A. Baracus
June 19, 2008 at 10:46 am
Here is the proc I have so far, inserting only one row into the ServerStatus table, with the basic server information from Server_Master. How do I get this specific data appended/joined/whatever with the data from ServerSteps (appended to 40 rows)?
/* This procedure will be executed whenever a new row is inserted into dbo.Server_Master.
The result will be inserting new information into the dbo.Server_Status table. Information
from dbo.Server_Master needs to be appended to every row from dbo.ServerSteps, and then all
rows inserted into dbo.Server_Status
*/
CREATE PROCEDURE dbo.usp_NewServerInsert
AS
-- Capture the ServerID (identity column) value of the server row that was just entered into
-- into the Server_Master table, causing the trigger to fire and execute this stored proc. This
-- ServerID will be used to match up the Server_Master and Server_Status tables. There is only one row
-- per server in Server_Master, whereas there will be many rows per server (40) in Server_Status.
DECLARE @CurrentServerID smallint
SELECT @CurrentServerID = (SELECT MAX(ServerID) FROM dbo.Server_Master)
INSERT INTO dbo.ServerStatus (ServerID, ServerName, SerialNumber, OperatingSystem)
SELECT ServerID, ServerName, SerialNumber, OperatingSystem
FROM dbo.Server_Master
WHERE ServerID = @CurrentServerID
GO
--Now what? How do I combine this info with all rows from dbo.ServerSteps table before inserting to dbo.ServerStatus?
"Got no time for the jibba jabba!"
-B.A. Baracus
June 19, 2008 at 11:24 am
Well, I have no idea if this is the best thing to do, but this seemed to accomplish what I wanted. This is probably the least efficient query ever created, or something..... but I ended up being able to use a sort of bogus outer join (not joining on anything). Any suggestions or help is still much appreciated.
ALTER PROCEDURE [dbo].[usp_NewServerInsert]
AS
DECLARE @CurrentServerID smallint
SELECT @CurrentServerID = (SELECT MAX(ServerID) FROM dbo.Server_Master)
INSERT INTO dbo.ServerStatus (ServerID, ServerName, SerialNumber, OperatingSystem, StepNumber, StepExit, Team)
SELECT master.ServerID, master.ServerName, master.SerialNumber, master.OperatingSystem, step.StepID, step.ExitStep, step.Team
FROM dbo.Server_Master master
FULL OUTER JOIN dbo.ServerSteps step
ON master.ServerID <> 0
WHERE master.ServerID = @CurrentServerID
"Got no time for the jibba jabba!"
-B.A. Baracus
June 19, 2008 at 6:53 pm
This will apply cross-join to Servers and steps, so every server will have every step assigned, and it will insert into the table all records from that cross-join which are not already there.
All servers and all steps in one go.
ALTER PROCEDURE [dbo].[usp_NewServerInsert]
AS
INSERT INTO dbo.ServerStatus
(ServerID, ServerName, SerialNumber, OperatingSystem, StepNumber, StepExit, Team)
SELECT master.ServerID, master.ServerName, master.SerialNumber, master.OperatingSystem, step.StepID, step.ExitStep, step.Team
FROM dbo.Server_Master master, dbo.ServerSteps step
WHERE NOT EXISTS (select 1 from dbo.ServerStatus ST
WHERE ST.ServerID = master.ServerID AND ST.StepNumber = step.StepID
)
GO
But why you need this table for?
It's just another example of wasted resources.
A view having SELECT statement from this SP will do the same.
If you mean to record dates and results of every step performed it's bettter to have LOG table where you'll record steps as they heppened on servers:
ServerID, StepID, DateCompleted, ResultID, CommentID
Then you will have full track of all events on your servers, not just last record for every step.
_____________
Code for TallyGenerator
June 20, 2008 at 6:08 am
The tables are being used as part of a business process. The table needs a row for each of these steps that are part of a server build process. Each step will be marked complete using an InfoPath form by the appropriate team - Time, Date, Individual responsible, Status, Comments, etc will all be added to the appropriate row for whatever step is being completed. Then the form will make the next step "active" and send an email to the appropriate team. There's more to it than this, but basically this is a streamlining of a current process to use InfoPath and SQL Server instead of forwarding an excel sheet all over the company to the appropriate parties at the appropriate time... this will now be centralized and tracked.
Don't need a record of every change to the server, only these steps, which are already pre-defined.
Again, not my project, so I can't attest to the design, just helping out. Thanks for your reply, though.
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply