Database

Rob Graham wrote:

But you can afford to let the database grow by 20% increments?

Rob Graham wrote:

It is better to run big dbs like this at a fixed size (no growth), and run a free space monitor / purge routine on a regular bas

Dayekh wrote:

I want to STORE the TimeEstimate FROM the Request table INTO the QuoteRequest Table WHENEVER the AddQUOTE Stored Procedure is executed. This will effectively be populating the QuoteRequest Table with records of all the Requests which have been Quoted, and hence will allow me to display this info in a datagrid.

I don't know how to go about doing that.

Colin Angus Mackay wrote:

When you run the stored procedure, does it not already populate QuoteRequest rows?

QuoteRequestID<br />
RequestID<br />
QuoteID<br />
NoOfQuoteDays

Dayekh wrote:

I can do insert/update/delete SPs, but in this instance, I think I will need an insert SP within an insert SP if you see my meaning.

CREATE PROCEDURE dbo.MyStoredProc
@a int,
@b int,
@c int,
@d int,
@e int,
@f int,
@g int,
AS
    -- Do and insert
    INSERT MyTable(a,b,c) VALUES (@a, @b, @c);
 
    -- Do an update
    UPDATE MyOtherTable
        SET d = @d, e = @e, f = @f
        WHERE g = @g;
GO

CREATE TRIGGER MyTrigger ON dbo.vwMyView<br />
INSTEAD OF INSERT AS<br />
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @MyVal NVARCHAR(50)<br />
<br />
SELECT @MyVAL = MyVal FROM INSERTED<br />
<br />
IF @MyVAL IS NOT NULL<br />
BEGIN<br />
INSERT INTO MyTable VALUES (MyVAL) FROM INSERTED<br />
END

CREATE TABLE e1
( id INT NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(20) NOT NULL,
enabled BIT NOT NULL,
 CONSTRAINT [PK_e1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [T1](
	[T1ID] [int] NOT NULL,
	[E1ID] [int] NOT NULL,
	name VARCHAR(20) NOT NULL,
	description VARCHAR(20) NOT NULL,
	enabled BIT NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [T1]  WITH NOCHECK ADD  CONSTRAINT [FK_e1] FOREIGN KEY([E1ID])
REFERENCES E1 ([ID])
GO

INSERT INTO [e1] ([id],[name],[description],[enabled])
     VALUES (1, 'Test1','Desc1',1)

INSERT INTO [e1] ([id],[name],[description],[enabled])
     VALUES (2, 'Test2','Desc2',1)

INSERT INTO [e1] ([id],[name],[description],[enabled])
     VALUES (3, 'Test3','Desc3',1)

INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
     VALUES(1,2,'TTest1','TDesc1',1)

INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
     VALUES(1,8,'TTest8','TDesc8',1)

update 
TMP_TABLE_1
set 
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
  ON A.CLASS_NO = B.STUDENT_NO
where 
<s>A.CLASS_NO *= B.STUDENT_NO
and</s> A.REQUEST_ID = @requestID