ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A Transact-sql script to create a database, add two tables to the database, and populate each table with some sample data.

USE master
--Drop sample database (WSEmpPhones)
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = N'WSEmpPhones')
DROP DATABASE WSEmpPhones
GO

--Create a sample database (WSEmpPhones)
CREATE DATABASE WSEmpPhones
ON
(NAME = WSEmpPhones_dat,
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\WSEmpPhones_dat.mdf',
SIZE = 1)
LOG ON
(NAME = WSEmpPhones_log,
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\WSEmpPhones_log.ldf',
SIZE = 1,
MAXSIZE = 5)
GO


Use WSEmpPhones
--Drop EmpPhones table if it exists already
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'EmpPhones')
DROP TABLE EmpPhones
GO

--Create EmpPhones Table
CREATE TABLE EmpPhones
(
EmployeeID int IDENTITY NOT NULL,
Fname nvarchar(20),
Lname nvarchar(40),
Phone int NOT NULL,
Password nvarchar(44) NOT NULL,
CONSTRAINT ck_Phone_id CHECK (Phone > 4000 and Phone < 5001),
CONSTRAINT pk_EmpPhones_id PRIMARY KEY CLUSTERED (EmployeeID),
CONSTRAINT tele_id UNIQUE NONCLUSTERED (Phone)
)
GO

--Insert with default EmployeeID values
INSERT EmpPhones VALUES ('Rick', 'Dobson', 4101, 'passrdobson')
INSERT EmpPhones (Fname, Lname, Phone, Password)
VALUES ('Virginia', 'Dobson', 4102, 'passvdobson')


--Reset EmployeeID value while inserting a row,
--and insert a new row from the reset value
SET IDENTITY_INSERT EmpPhones ON
INSERT EmpPhones (EmployeeID, Fname, Lname, Phone, Password)
VALUES (51, 'Tony', 'Hill', 4201, 'passthill')
SET IDENTITY_INSERT EmpPhones OFF
INSERT EmpPhones (Fname, Lname, Phone, Password)
VALUES ('Glen', 'Hill', 4202, 'passghill')

--Reset EmployeeID value again while inserting a row
SET IDENTITY_INSERT EmpPhones ON
INSERT EmpPhones (EmployeeID, Fname, Lname, Phone, Password)
VALUES (101, 'Dee', 'Simmons-Edelstein', 4301, 'passdsimmons')
SET IDENTITY_INSERT EmpPhones OFF

--Drop AppVariables table if it exists already
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'AppVariables'
AND type = 'U')
DROP TABLE AppVariables
GO

--Create AppVariables Table
CREATE TABLE AppVariables (
SuperPassword nvarchar(44)
)
GO

--Insert an initial value for SuperPassword
INSERT AppVariables (SuperPassword) VALUES('I can get in')
GO

Want to understand  SQL Server so that you can program it to do more tasks like this?  Get Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 2001 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.