How to create a table structure that can store de

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create a table structure that can store de

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-11 : 07:00:01

Hello All,

I need help creating a table structure that can accommodate all detailed recordset regardless of the column size or the record count.
I’ve provided the script that creates my current table structure to try to illustrate my problem.
Table Structure:
USE [TestData]
GO
/****** Object: Table [dbo].[Detailed_Audit] Script Date: 06/11/2009 06:46:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Detailed_Audit](
[Detailed_Audit_ID] [int] NOT NULL,
[Summary_Audit_ID] [int] NOT NULL,
[Detailed_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

-----------------------------------------------------------------------------------------
USE [TestData]
GO
/****** Object: Table [dbo].[Summary_Audit] Script Date: 06/11/2009 06:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Summary_Audit](
[Summary_Audit_Id] [int] NOT NULL,
[Audit_Date] [datetime] NOT NULL,
[Measure_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

Below are samples of detail recordset that need to be stored in the Detailed_Audit table and reference the summary_Audit table.

TAX_ID NAME_COUNT
000-00-000 2
00-0000000 2
01-0000000 2
02-0000000 2
03-0000000 2
04-0000000 2

ADDR1 CITY STATE_ID ZIP_CODE
123 Lucky St. WESTBURY NY 11590
732 Barns Rd. WESTBURY NY 11590
143 East 7th st. New York NY 10023

Please advice. Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 07:16:15

I'm sorry -- I'm still at a complete loss at what you want to do here.

The sample data you provided seems very easy to split into obvious columns. I've no idea what it relates to for your Audit tables though.

Maybe it would be a good idea to tell us what you want to have in your audit tables at the end based on this sample data?

Sorry,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-11 : 07:36:39

Thanks for the quick response Charlie. The problem is the Detail_Audit table will be storing detail information not knowing the number of columns in the record set. The detail sample that I provided is just an example of what kind of data will be collected. Based on the sample I provided the first detail information has two columns and the second one has 4 columns. The question is how can I build a table structure that is dynamic that can accommodate my needs (not knowing the number of columns before hand)? Hopefully this is a little clearer. Please advice. Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 07:47:05

Do you need to do any kind of work on these 'columns' I'd doubt it if you don't know what they are going to be?

If not and they are just a set of text then why not go for 1 column
of NVARCHAR(MAX) and store the whole lot in it (including whatever delimiters are there).

The only real other option is to make up a table with key value pairs and a foreign key to the audit ID.

Something like:

CREATE TABLE Audit_ifo (
[AuditId] INT -- this is your foreign key to the audit table Id
, [KeyName] NVARCHAR(255)
, [KeyValue] NVARCHAR(MAX)
)

Note that because you need to store many different datatypes you have to choose something universal. NVARCHAR(MAX) is probabyl good enough if you are only dealing with textual data.

If you don't need to do anything with the data except store it against some Audit Id then I'd go for the first approach and just add a [details] NVARCHAR(MAX) column to the relavent table. Better for performance reasons than the key value pair approach.

If you actually need to do something with the info you are collecting then maybe you should go back to the planning board -- there are seldom situations where you really *need* to deal with unknown columns like this.

Feel free to bounce some ideas.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 07:49:26

You could also probably use some XML based schemes. I've never got into them but I know others here are familiar with them. Visakh16 is a good bet.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources