Sql updating table from temp table

Rated 4.94/5 based on 873 customer reviews

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.You should just use this as a starting point: CREATE TABLE dbo.Sharing them here will hopefully prevent you from re-inventing any wheels when populating your own tables.

If your company doesn't recognize Easter, you can skip ahead; if it does, you can use the following function, which will return the Easter holiday dates for any given year: CREATE FUNCTION dbo. Creating a dimension or calendar table for business dates and fiscal periods might seem intimidating at first, but once you have a solid methodology in line, it can be very worthwhile.

Get Easter Holidays(@year INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( WITH x AS ( SELECT [Date] = CONVERT(DATE, RTRIM(@year) '0' RTRIM([Month]) RIGHT('0' RTRIM([Day]),2)) FROM (SELECT [Month], [Day] = Days To Sunday 28 - (31 * ([Month] / 4)) FROM (SELECT [Month] = 3 (Days To Sunday 40) / 44, Days To Sunday FROM (SELECT Days To Sunday = paschal - ((@year @year / 4 paschal - 13) % 7) FROM (SELECT paschal = epact - (epact / 28) FROM (SELECT epact = (24 19 * (@year % 19)) % 30) AS epact) AS paschal) AS dts) AS m) AS d ) SELECT [Date], Holiday Name = 'Easter Sunday' FROM x UNION ALL SELECT DATEADD(DAY,-2,[Date]), 'Good Friday' FROM x UNION ALL SELECT DATEADD(DAY, 1,[Date]), 'Easter Monday' FROM x ); (You can adjust the function easily, depending on whether they recognize just Easter Sunday or also Good Friday and/or Easter Monday. There are many ways to do this; some will subscribe to the idea that many of these date-related facts can be derived at query time, or at least be non-persisted computed columns.

There is also another tip here that will show you how to determine the date for Mardi Gras, given the date for Easter.) Now, to use that function to mark the Easter holidays in the calendar table: ; WITH x AS ( SELECT d.[Date], d. You will have to decide if the values are calculated often enough to justify the additional space on disk and in the buffer pool.

I've thought of a complicated way, creating a temp table with a "random number" column, copying my table into that, looping through the temp table and updating each row with It's always good to keep in mind that newid() isn't a really good pseudorandom number generator, at least not nearly as good as rand().

But if you just need some vaguely randomish samples and don't care about mathematical qualities and such, it'll be good enough.

Leave a Reply