User Specific Persistent Temporary Table
-
I've an application I'm developing in Vb.Net with SQLServer 2000 which displays a grid of pivot data to the user. Database connections are kept short and do not persist.
Depending on the user's parameters each line in the grid will display a few mandatory fields followed by anything from 1 to hundreds of columns of numeric data.
I'm ok creating the table, populating it with data in a user defined procedure and then displaying the data.
My problem is that there'll be multiple users for the application and ideally I'd like each user to create and populate their own table on the database but how can I do this without using dynamic SQL throughout the user defined procedure ?
My current approach is that the user defined procedure;
- Starts a transaction
- Creates a table
- Populates the table
- Renames the table to a user specific name
- Commits the transaction
I'm worried that different users calling the same user defined procedure could cause problems.
Is it possible to rename a #temporary table to a real database table ?
Any help appreciated.
-
Because the temp table resided in TempDB, you will not be able to rename the table using sp_rename as sp_rename will not allow you to operate on objects outside of the current DB. You can use SELECT INTO to create a static table.
SELECT *
INTO StaticTable
FROM #tmpTable
Your problem then will be coming up with the table names to keep the static tables unique for each user. Having code create tables on the fly is probably not the best solution for you. You may get more help if you can give more detail on what you want to do. Include DDL and sample data if you could.
-
creating permanent tables at runtime is a really bad idea. the database is the foundation of your app. if the schema is changing at runtime, you are building on a shaky foundation indeed.
---------------------------------------
elsasoft.org -
Normally I'd agree with with you but can you suggest a better method so that I can pivot (cross-correlate) data, return a table definition to a .Net application so that it can use it to define a dataset which is then displayed & updated in a Windows grid ?
The database tables are;
1. Defect Group ==> Defect Type (outer join)==> Sample Item AND
2. Sample Group (outer join)==> Sample Item
The users want a Windows grid with Defect Group then Type down the left hand side and Sample Group along the top with the Sample Item values shown where columns & rows coincide.
i.e. An Excel spreadsheet layout with the same functionality.
There aren't actual Sample Items records for each coincidence but the user can add them as required, amend or delete those existing.
So to give this functionality I'm generating a Db table which actually has fields for every coincidence and using this as the data source for the grid.
When a user updates the grid they update the local dataset AND the underlying Sample Item record.
I've got everything working fine but I'm concerned about user contention when these 'temporary' Db tables are being created.
Your criticism is duly noted but practical suggestions would be more helpful.
-
Please provide:
1. DDL for tables involved
2. sample data for each table, in the form of insert statements, not simply cut/paste from the results window of query analyzer.
3. expected output for your pivot query, based on sample data provided in (2)
if you provide this, i'm sure you'll get some help. without these details, you probably won't.
---------------------------------------
elsasoft.org -
Sorry if it took a while for me to get this together but the details were at work.
The following gives a simplified example of what I'm trying to achieve, the real-life code & implementation has much more complexity and gotchas.
Table definitions;
-- Table definitions
CREATE TABLE [dbo].[DefectType] (
[DefectTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortSeq] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SampleGroup] (
[SampleGroupId] [int] IDENTITY (1, 1) NOT NULL ,
[SampleDate] [datetime] NOT NULL ,
[SubLotted] [bit] NOT NULL ,
[ResetPoint] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SampleItem] (
[SampleItemId] [int] IDENTITY (1, 1) NOT NULL ,
[SampleGroupId] [int] NOT NULL ,
[DefectTypeId] [int] NOT NULL ,
[Quantity] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pvtCol] (
[pvtColId] [int] IDENTITY (1, 1) NOT NULL ,
[ColNo] [int] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateGrid] [int] NOT NULL ,
[SampleGroupId] [int] NOT NULL ,
[SampleDate] [datetime] NOT NULL ,
[SubLotted] [bit] NOT NULL ,
[ResetPoint] [bit] NOT NULL
) ON [PRIMARY]
GO
-- Data Load
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Blemish', 1)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Labels', 2)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Stain', 3)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Sunburn', 4)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Proximity Mark', 5)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Surface Deposit', 6)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Pitting', 7)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Alternia', 8)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Soft Defects', 9)
INSERT INTO DefectType (Description, SortSeq) VALUES (N'Over-Ripe', 10)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 02:55:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:02:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:09:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:16:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:23:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:30:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:37:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:44:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:51:00',102), 0, 0)
INSERT INTO SampleGroup(SampleDate, SubLotted, ResetPoint) VALUES (CONVERT(DATETIME,'12/04/2007 03:58:00',102), 0, 0)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (1, 1, 1)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (1, 2, 2)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (3, 5, 2)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (6, 2, 1)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (8, 8, 1)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (9, 2, 1)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (9, 3, 5)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (10, 1, 1)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (10, 10, 3)
INSERT INTO SampleItem(SampleGroupId, DefectTypeId, Quantity) VALUES (10, 7, 1)
-- Stored procedure
ALTER PROCEDURE dbo.uspPvtItems
(
@UpdateGrid INT,
@Username VARCHAR(50),
@FromDate DATETIME,
@ToDate DATETIME
)
AS
SET NOCOUNT ON
DECLARE @SampleGroupLowerDate DateTime,
@SampleGroupUpperDate DateTime
-- Set the criteria for creation of the Columns data depending on whether we're creating data for an update grid or a list grid
IF @UpdateGrid = 1
BEGIN
SELECT @SampleGroupLowerDate = MIN(SampleDate)
FROM
(SELECT TOP 6 SampleDate
FROM dbo.SampleGroup
WHERE
(SubLotted = 0) AND
(SampleDate >=
(SELECT ISNULL(MAX(SampleDate), '01/01/2000')
FROM dbo.SampleGroup
WHERE
(ResetPoint = 1)))
ORDER BY SampleGroup.SampleDate DESC)
DERIVEDTBL
END
ELSE
BEGIN
SET @SampleGroupLowerDate = @FromDate
END
SET @SampleGroupUpperDate = @ToDate
print @SampleGroupLowerDate
print @SampleGroupUpperDate
DELETE FROM pvtCol WHERE Username = @Username
-- Populate the columns table
INSERT INTO pvtCol
(
Username,
UpdateGrid,
SampleGroupId,
SampleDate,
SubLotted,
ResetPoint
)
SELECT
@Username,
@UpdateGrid,
SampleGroup.SampleGroupId,
SampleGroup.SampleDate,
SampleGroup.SubLotted,
SampleGroup.ResetPoint
FROM SampleGroup
WHERE
SampleGroup.SampleDate >= @SampleGroupLowerDate AND
SampleGroup.SampleDate <= @SampleGroupUpperDate
ORDER BY SampleGroup.SampleDate
-- Set the ColNo field values for the inserted records
DECLARE
@ColNo INT,
@pvtColId INT
DECLARE pvtCol_Cursor CURSOR FOR
SELECT pvtColId
FROM pvtCol
WHERE UserName = @UserName
ORDER BY pvtColId
OPEN pvtCol_Cursor
SET @ColNo = 1
FETCH NEXT FROM pvtCol_Cursor INTO @pvtColId
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE pvtCol SET ColNo = @ColNo WHERE pvtColId = @pvtColId
FETCH NEXT FROM pvtCol_Cursor INTO @pvtColId
SET @ColNo = @ColNo + 1
END
CLOSE pvtCol_Cursor
-- Create and populate the Rows table
BEGIN TRANSACTION
DECLARE
@RowTableFullName AS VARCHAR(50),
@RowTableName AS VARCHAR(50),
@sql VARCHAR(1000)
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[pvtRow]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
EXEC('DROP TABLE pvtRow')
END
-- Set the name of the Row table and delete any previously created table versions for this user
IF @UpdateGrid = 1
SET @RowTableName = 'pvtRowUpdate_'
ELSE
SET @RowTableName = 'pvtRowList_'
SET @RowTableName = @RowTableName + @Username
SET @RowTableFullName = '[dbo].[' + @RowTableName + ']'
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(CAST(@RowTableFullName AS VARCHAR(50))) and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
BEGIN
EXEC('DROP TABLE ' + @RowTableFullName)
END
CREATE TABLE pvtRow
(
pvtRowId INT IDENTITY (0, 1) PRIMARY KEY,
DefectTypeId INT NOT NULL,
Type VARCHAR(50) NOT NULL
)
-- Load the Rows table data
INSERT INTO pvtRow
(
DefectTypeId,
Type
)
SELECT
DefectType.DefectTypeId,
DefectType.Description
FROM
DefectType
ORDER BY dbo.DefectType.SortSeq
CREATE UNIQUE INDEX IX_pvtRow ON pvtRow (DefectTypeId)
DECLARE
@SampleGroupId INT
DECLARE SampleGroupCursor CURSOR FOR
SELECT ColNo,
SampleGroupId
FROMpvtCol
WHERE Username = @Username
ORDER BY ColNo
OPEN SampleGroupCursor
FETCH NEXT FROM SampleGroupCursor INTO @ColNo, @SampleGroupId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE pvtRow ADD Col' + CAST(@ColNo AS VARCHAR(2)) + ' INT NULL DEFAULT 0'
EXEC (@SQL)
SET @sql ='UPDATE pvtRow
SET col' + CAST(@ColNo AS VARCHAR(2)) + ' =
SampleItem.Quantity
FROM SampleItem
WHERE
SampleItem.DefectTypeId = pvtRow.DefectTypeId AND
SampleItem.SampleGroupId = ' + CAST(@SampleGroupId AS Varchar(8))
EXEC (@SQL)
FETCH NEXT FROM SampleGroupCursor INTO @ColNo, @SampleGroupId
END
-- Rename the tables created to be particular for the current user
SET @sql = 'sp_rename ' + '''' + 'pvtRow' + '''' + ', ' + '''' + @RowTableName + ''''
EXEC (@SQL)
COMMIT TRANSACTION
After running the procedure using parameters of 1, 'admin', '4-Dec-2007', '5-Dec-2007'
I end up with;
- 6 records in the pvtCol table which has a static record layout
- 10 records in the pvtRowUpdate_admin table which has the following generated layout;
CREATE TABLE [dbo].[pvtRowUpdate_admin] (
[pvtRowId] [int] IDENTITY (0, 1) NOT NULL ,
[DefectTypeId] [int] NOT NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col1] [int] NULL ,
[Col2] [int] NULL ,
[Col3] [int] NULL ,
[Col4] [int] NULL ,
[Col5] [int] NULL ,
[Col6] [int] NULL
) ON [PRIMARY]
Depending on the user parameters and the data available the pvtRowUpdate_admin table could have zero to loads of 'colN' fields.
The problem is intriguing and I think mainly solved apart from the worry that more than one user may try and create the intermediate pvtRow table at the same time.
Suggestions appreciated.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply