SQL, Visual Studio, microsoft

Edit as required…

USE master
go
 
IF  EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tCalendar')
BEGIN
DROP TABLE tCalendar
END
go
 
CREATE TABLE tCalendar (
DateID INT IDENTITY(1,1) CONSTRAINT tCalendar_PK PRIMARY KEY CLUSTERED,
DATE DATETIME,
Holiday BIT DEFAULT 0,
Workday BIT DEFAULT 0)
go
CREATE UNIQUE NONCLUSTERED INDEX
tCalendar_date_N_Idx ON tCalendar(DATE)
go
 
--Populate all days
 
DECLARE @n INT
DECLARE @maxn INT
DECLARE @begindate DATETIME
SET @n =1
SET @maxn=36500    -- Number of days added to the calendar
SET @begindate =CONVERT(DATETIME,'01/01/1995')
-- Initial date for the first run is todays date
-- or Jan 1st
SET @begindate =@begindate -1
 
WHILE @n <= @maxn
BEGIN
INSERT INTO tCalendar(DATE) SELECT @begindate+@n
SET @n=@n+1
END
 
--update the holiday and workday flags
 
go
UPDATE tCalendar
SET holiday=1 WHERE DATENAME(dw,DATE) in ('Saturday','Sunday')
go
UPDATE tCalendar
SET workday=1 WHERE holiday=0
go
 
SELECT * FROM tCalendar

Category: SQL, Visual Studio, microsoft author: admin comments: No Comments

There is no easy way that I am aware of (in MS SQL 2000). You basically have to create a table with an additional identity field and import the data. Here is how you do it:

-From *Enterprise Manager, backup the original table by renaming it.
-From Query Analyzer, create new table…
-Right click the old table “Script Object to new window as ” -> “create”.
-Edit the ALL table names in the query to the original. (this makes a copy of the table structure)
-Execute.
-In Enterprise manager create new ID (identity column) and set the identity property to “yes, not for replication” in the newly created table (you may also want to set this column as the primary key).
-Import the data…
-Run this query in Query Analyzer, replacing the appropriate table names and column names. NOTE: the column names must be in the same order for both tables!
INSERT INTO [new_table] (list_all_the_column_names_to_import) SELECT list_all_the_column_names_to_import FROM [old_table]

* If you know your SQL query lingo well enough you can do everything from Query Analyzer.

:)

Category: SQL, microsoft author: admin comments: No Comments