Updating data from one table to another in sql server
This is a one-time population, so I'm not worried about the costs of using intermediate storage like temp tables.I like to materialize all of the columns to disk, rather than rely on computed columns, since the table becomes read-only after initial population.
The CHECK constraint is used to limit the value range that can be placed on a column.One of the biggest objections I hear to calendar tables is that people don't want to create a table.I can't stress enough how cheap a table can be in terms of size and memory usage, especially as storage continues to be larger and faster, compared to using all kinds of functions to determine date-related information on every single query.Or the Check constraint specifies the conditions for the data inserted into a column.Each row inserted into a table or each value updating the value of a column must meet these conditions.So I'm going to do a lot of those calculations during the initial population of the #temp table: DECLARE @Start Date DATE = '20000101', @Number Of Years INT = 30; -- prevent set or regional settings from interfering with -- interpretation of dates / literals SET DATEFIRST 7; SET DATEFORMAT mdy; SET LANGUAGE US_ENGLISH; DECLARE @Cutoff Date DATE = DATEADD(YEAR, @Number Of Years, @Start Date); -- this is just a holding table for intermediate calculations: CREATE TABLE #dim ( [date] DATE PRIMARY KEY, [day] AS DATEPART(DAY, [date]), [month] AS DATEPART(MONTH, [date]), First Of Month AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)), [Month Name] AS DATENAME(MONTH, [date]), [week] AS DATEPART(WEEK, [date]), [ISOweek] AS DATEPART(ISO_WEEK, [date]), [Day Of Week] AS DATEPART(WEEKDAY, [date]), [quarter] AS DATEPART(QUARTER, [date]), [year] AS DATEPART(YEAR, [date]), First Of Year AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)), Style112 AS CONVERT(CHAR(8), [date], 112), Style101 AS CONVERT(CHAR(10), [date], 101) ); -- use the catalog views to generate as many rows as we need INSERT #dim([date]) SELECT d FROM ( SELECT d = DATEADD(DAY, rn - 1, @Start Date) FROM ( SELECT TOP (DATEDIFF(DAY, @Start Date, @Cutoff Date)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 -- on my system this would support Now these pre-calculated values can help to derive all of the other materialized columns you might want in your calendar table.
The following is just a sampling of the things I see most commonly; I am sure that you do not need all of these columns, and that there might be other columns you need.
Or on any version or edition, you could put the calendar table into its own filegroup (or database), and mark it as read-only after initial population (this won't force the table to stay in memory all the time, but it will reduce other types of contention).
Serializable and Repeatable Read isolation levels offer protections so your users won’t see weird or incorrect data — but there are tradeoffs for those protections.
You should just use this as a starting point: CREATE TABLE dbo.
Date Dimension ( Date Key INT NOT NULL PRIMARY KEY, [Date] DATE NOT NULL, [Day] TINYINT NOT NULL, Day Suffix CHAR(2) NOT NULL, [Weekday] TINYINT NOT NULL, Week Day Name VARCHAR(10) NOT NULL, Is Weekend BIT NOT NULL, Is Holiday BIT NOT NULL, Holiday Text VARCHAR(64) SPARSE, DOWIn Month TINYINT NOT NULL, [Day Of Year] SMALLINT NOT NULL, Week Of Month TINYINT NOT NULL, Week Of Year TINYINT NOT NULL, ISOWeek Of Year TINYINT NOT NULL, [Month] TINYINT NOT NULL, [Month Name] VARCHAR(10) NOT NULL, [Quarter] TINYINT NOT NULL, Quarter Name VARCHAR(6) NOT NULL, [Year] INT NOT NULL, MMYYYY CHAR(6) NOT NULL, Month Year CHAR(7) NOT NULL, First Day Of Month DATE NOT NULL, Last Day Of Month DATE NOT NULL, First Day Of Quarter DATE NOT NULL, Last Day Of Quarter DATE NOT NULL, First Day Of Year DATE NOT NULL, Last Day Of Year DATE NOT NULL, First Day Of Next Month DATE NOT NULL, First Day Of Next Year DATE NOT NULL ); GO -- create other useful index(es) here Now to populate this table from our #dim object, it is a relatively straightforward INSERT/SELECT; still, you'll see why I pre-calculated some of the values, since many of the expressions are used multiple times: INSERT dbo.
I also always explicitly set things like DATEFORMAT, DATEFIRST, and LANGUAGE to avoid ambiguity, default to English for month and day names, and assume that quarters for the fiscal year align with the calendar year.