View Vs Join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

View Vs Join

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-10-15 : 11:48:10

Hi everyone
Iam not sure whether Iam thinking in the right way.Need assistance

we were using a table to store policy information.It stores each policy in a column and has its limitations as it could only save 24 policies for a particular request as there are only 24 columns.we made enhancements to our website and created a new table that could save policies as rows which means we have no limitations to store policies

The foreign key in both the tables is the reqnum column

do I have to create a join between these tables or a view so that the stored procedure in our website gets policy information whereever it exists based on the reqnum

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-15 : 13:58:54

Sounds to me like you should transform the data in your original de-normalized table into your new normalized one. Then you only have the info stored in one place.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 02:34:58

If you're asking a way to transform your already existing column format table to new table then you should be using UNPIVOT operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-10-18 : 09:19:42

I dont really see the historical data that is present in the old table in our downstream process any more.So do not want to transform the data from the old table to the new table

we are looking to create a union query between the old table and the new table.
can someone help me write a union query

Old Table Schema is
USE [UnderWriting]
GO

/****** Object: Table [dbo].[platform] Script Date: 10/18/2010 08:14:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[platform](
[requestid] [numeric](18, 0) NOT NULL,
[pltfmcos] [varchar](3) NULL,
[pltfmunet] [varchar](3) NULL,
[pltfmprm] [varchar](3) NULL,
[pltfmoth] [varchar](3) NULL,
[zh1] [varchar](10) NULL,
[zh2] [varchar](10) NULL,
[zh3] [varchar](10) NULL,
[zh4] [varchar](10) NULL,
[zh5] [varchar](10) NULL,
[zh6] [varchar](10) NULL,
[zh7] [varchar](10) NULL,
[zh8] [varchar](10) NULL,
[zh9] [varchar](10) NULL,
[zh10] [varchar](10) NULL,
[zh11] [varchar](10) NULL,
[zh12] [varchar](10) NULL,
[zh13] [varchar](10) NULL,
[zh14] [varchar](10) NULL,
[zh15] [varchar](10) NULL,
[zh16] [varchar](10) NULL,
[zh17] [varchar](10) NULL,
[zh18] [varchar](10) NULL,
[zh19] [varchar](10) NULL,
[zh20] [varchar](10) NULL,
[zh21] [varchar](10) NULL,
[zh22] [varchar](10) NULL,
[zh23] [varchar](10) NULL,
[zh24] [varchar](10) NULL,
[wh1] [varchar](10) NULL,
[wh2] [varchar](10) NULL,
[wh3] [varchar](10) NULL,
[wh4] [varchar](10) NULL,
[wh5] [varchar](10) NULL,
[wh6] [varchar](10) NULL,
[wh7] [varchar](10) NULL,
[wh8] [varchar](10) NULL,
[wh9] [varchar](10) NULL,
[wh10] [varchar](10) NULL,
[wh11] [varchar](10) NULL,
[wh12] [varchar](10) NULL,
[wh13] [varchar](10) NULL,
[wh14] [varchar](10) NULL,
[xh1] [char](3) NULL,
[xh2] [char](3) NULL,
[xh3] [char](3) NULL,
[xh4] [char](3) NULL,
[xh5] [char](3) NULL,
[xh6] [char](3) NULL,
[xh7] [char](3) NULL,
[xh8] [char](3) NULL,
[xh9] [char](3) NULL,
[xh10] [char](3) NULL,
[xh11] [char](3) NULL,
[xh12] [char](3) NULL,
[xh13] [char](3) NULL,
[xh14] [char](3) NULL,
[xh15] [char](3) NULL,
[xh16] [char](3) NULL,
[xh17] [char](3) NULL,
[xh18] [char](3) NULL,
[xh19] [char](3) NULL,
[xh20] [char](3) NULL,
[xh21] [char](3) NULL,
[xh22] [char](3) NULL,
[xh23] [char](3) NULL,
[xh24] [char](3) NULL,
CONSTRAINT [PK__platform] PRIMARY KEY CLUSTERED
(
[requestid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[platform] WITH NOCHECK ADD CONSTRAINT [FK__CUSTOMERPLAT_reqid] FOREIGN KEY([requestid])
REFERENCES [dbo].[REPORT_REQUEST] ([requestid])
GO

ALTER TABLE [dbo].[platform] CHECK CONSTRAINT [FK__CUSTOMERPLAT_reqid]
GO

New Table

CREATE TABLE [dbo].[REQUEST_POLICIES](
[RequestId] [numeric](18, 0) NOT NULL,
[CSN] [varchar](25) NOT NULL,
[PolicyName] [varchar](100) NULL,
[LOB] [varchar](50) NULL,
[Platform] [varchar](50) NULL,
[FundingType] [varchar](10) NULL,
[Salesoffice] [varchar](20) NULL,
[OrigEffDate] [datetime] NULL,
[NextRenewal] [datetime] NULL,
[SubscrCount] [int] NULL,
[MembrCount] [int] NULL,
CONSTRAINT [PK_REQUEST_POLICIES_1] PRIMARY KEY CLUSTERED
(
[RequestId] ASC,
[CSN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[REQUEST_POLICIES] WITH CHECK ADD CONSTRAINT [FK_REQUEST_POLICIES_REPORT_REQUEST] FOREIGN KEY([RequestId])
REFERENCES [dbo].[REPORT_REQUEST] ([requestid])
GO

ALTER TABLE [dbo].[REQUEST_POLICIES] CHECK CONSTRAINT [FK_REQUEST_POLICIES_REPORT_REQUEST]
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:08:20

ok. even in that case you need to first unpivot data and get it into rows from columns and then take union with the other table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-10-19 : 13:23:37

Thanks.It worked

I created a view of the old table which looked similar to the new one and used union between the new table and old table to get this working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:32:48

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources