Moving record from view to a table

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

Moving record from view to a table

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-03-28 : 15:34:12

Hi All,
I have a view that contains 30 million records.I want to move the view to a table in my database using DTS,but it is taking a lot of time,and making my tempdb to grow fast in giga bytes.Please is there anyway i can copy this view into the table easily in minutes.The view structure and the table structure are the same.Also, how can I index a view and can I add unique key to a view.

Thanks All in advance.
Mokah

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-28 : 16:27:27

Why do you want to do this? You don't seem to understand what a view is. A view is a stored query, the data is already stored in tables (right click on the view in Enterprise Manager and select properties to see the query).

~Travis

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-03-29 : 03:59:10

Hi,
I know a view is a stored query.The data is a summary data which does not change, we need to put it in a table so as to add a primary key to it and also to index as many colums as possible,Eg, Zipcode data.We are doing this to make the search fast as the data in the view will not change for a long time.
Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 04:21:17

[code]select * into table_name from view_name[/code]
This will create the table & insert records into it. There after, create the primary key & necessary indexes.

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 04:23:13

"We are doing this to make the search fast"
Maybe you should take a look at the root problem rather than try to work around it. You should try to optimize your view query first.

You can post the view query here.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-03-29 : 07:14:41

Thanks, here is the view query.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_address]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_address]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vw_address
AS
SELECT DISTINCT
NEWID() AS AddressCode, dbo.ADDRESS.Outcode + ' ' + dbo.ADDRESS.Incode AS Postcode,
dbo.ADDRESS.[Number of Households] AS NumberofHouseholds, dbo.ADDRESS.[PO Box Number] AS POBoxNunber,
dbo.ADDRESS.[Small User Organisation] AS SmallUserOrganisation, dbo.ADDRESS.[Delivery Point Suffix] AS DeliveryPointSuffix,
dbo.ADDRESS.[Concatenation Idicator] AS ConcatenationIdicator, dbo.LOCALITY.[Post Town] AS PostTown,
dbo.LOCALITY.[Dependent Locality] AS DependentLocality, dbo.LOCALITY.[Double Dependent locality] AS DoubleDependentlocality,
dbo.ORGANISA.[Organisation Name] AS OrganisationName, dbo.ORGANISA.[Department Name] AS DepartmentName, dbo.PZONE001.County,
dbo.PZONE001.[Grid Reference East] AS GridReferenceEast, dbo.PZONE001.[Grid Reference North] AS GridReferenceNorth, dbo.PZONE001.District,
dbo.PZONE001.Ward, dbo.PZONE001.Country, dbo.PZONE001.[NHS Code] AS NHSCode, dbo.PZONE001.[NHS Region] AS NHSRegion,
dbo.THFARE.[Thoroughfare Name] AS ThoroughfareName, dbo.THDESC.[Thoroughfare Descriptor] AS ThoroughfareDescriptor,
dbo.THDESC.[Approved Abbreviation] AS ApprovedAbbreviation, dbo.DELPOINTALIASFLE.[Alias Text] AS deliverypoint,
dbo.DELPOINTALIASFLE.Category, dbo.SUBBNAME.[Sub Building Name] AS SubBuildingName, dbo.BNAME.[Building Name] AS BuildingName,
dbo.ADDRESS.[Building Number] AS BuildingNumber, dbo.DELPOINTALIASFLE.[Postcode Type] AS PostcodeType, dbo.DELPOINTALIASFLE.Currency,
dbo.DELPOINTALIASFLE.[Record Type], dbo.PZONE001.Outcode, dbo.PZONE001.Incode, dbo.PZONE001.[User Type] AS UserType
FROM dbo.ADDRESS LEFT OUTER JOIN
dbo.DELPOINTALIASFLE ON dbo.ADDRESS.[Address Key] = dbo.DELPOINTALIASFLE.[Address Key] AND
dbo.ADDRESS.[Organisation Key] = dbo.DELPOINTALIASFLE.[Organisation Key] AND
dbo.ADDRESS.[Postcode Type] COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.DELPOINTALIASFLE.[Postcode Type] LEFT OUTER JOIN
dbo.THFARE ON dbo.ADDRESS.[Thoroughfare Key] = dbo.THFARE.[Thoroughfare Key] LEFT OUTER JOIN
dbo.THDESC ON dbo.ADDRESS.[Thoroughfare Descriptor Key] = dbo.THDESC.[Thoroughfare Descriptor key] LEFT OUTER JOIN
dbo.PZONE001 ON dbo.ADDRESS.Outcode COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.PZONE001.Outcode AND
dbo.ADDRESS.Incode COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.PZONE001.Incode LEFT OUTER JOIN
dbo.ORGANISA ON dbo.ADDRESS.[Organisation Key] = dbo.ORGANISA.[Organisation Key] LEFT OUTER JOIN
dbo.LOCALITY ON dbo.ADDRESS.[Locality Key] = dbo.LOCALITY.[Locality Key] LEFT OUTER JOIN
dbo.BNAME ON dbo.ADDRESS.[Building Name Key] = dbo.BNAME.[Building Name Key] LEFT OUTER JOIN
dbo.SUBBNAME ON dbo.ADDRESS.[Sub Building Name Key] = dbo.SUBBNAME.[Sub Building Name Key]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-29 : 08:09:58

How are you going to summarize the data from this view? There is nothing to summarize!

In addition, if you try to "denormalize" your tables and store copies of data that is redundant, your resulting table will be huge with lots of unecessary, repeating data and even less efficient than if you just queried the origianl tables directly ... assuming, of course, that your current tables are well-indexed.

If you take this view and put it into a table, what have you gained?

If you want to store a summary of data for reporting, then you should summarize the *transactions* only, rolled up by the foreign key columns that you can later join to get descriptions other attributes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources