User Tools

Site Tools


notes:sql:createtable

CREATE TABLE

Create simple tables:

-- Example#1
IF OBJECT_ID('Books') IS NOT NULL DROP TABLE Books
GO
CREATE TABLE Books
(
    BookID INT PRIMARY KEY,
    Title VARCHAR(20) NULL,
    BookGuid UNIQUEIDENTIFIER
)
GO
 
-- Example#2
CREATE TABLE Books (BookId INT NOT NULL)
GO
 
-- Example#3
CREATE TABLE Customer
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerName NVARCHAR(100)
)
GO
 
-- Example #4
CREATE TABLE Elements
(
    ElementId VARCHAR(200) PRIMARY KEY,
    ElementType VARCHAR(50) NOT NULL DEFAULT '',
    PermLevel INT NOT NULL DEFAULT 1 CHECK (PermLevel IN (0, 1, 2, 3))
)
GO

Create parent-child tables:

-- Create Publisher table (parent)
CREATE TABLE Publisher
(
    PubId CHAR(2) PRIMARY KEY,
    -- Publisher name
    PubName NVARCHAR(250) NOT NULL DEFAULT '',
) ON [PRIMARY]
GO
 
-- Create Book table (child)
CREATE TABLE Book
(
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    -- Book publisher
    PubId CHAR(2) NOT NULL CONSTRAINT FK_Book_Publisher FOREIGN KEY REFERENCES Publisher(PubId),
    -- Book title (together with a subtitle) 
    Title NVARCHAR(250) NOT NULL,
    -- A comma-separated list of authors
    Authors NVARCHAR(250) NOT NULL,
    -- 13-digit ISBN (no spaces or separators)
    Isbn13 CHAR(13) NOT NULL,
    -- Date of publication
    PubDate DATETIME NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX UX_Book_Isbn13 ON Book(Isbn13)
GO

Create a table by copying its structure and data from another table:

-- Example #1
SELECT BookId INTO BookCopy FROM Book
 
-- Example #2
SELECT BookId INTO #book FROM Book
 
-- Example #3: Get the table structure and modify it
SELECT TOP 1 * INTO #customer FROM Customer
DELETE FROM #customer
ALTER TABLE #customer DROP COLUMN VersionNumber
ALTER TABLE #customer DROP COLUMN CustomerType

Create a table with a computed (derived) column:

IF OBJECT_ID('Items') IS NOT NULL DROP TABLE Items
GO
CREATE TABLE Items
(
    UnitPrice MONEY,
    Quantity INT,
    Discount FLOAT,
 
    -- Computed columns
    TotalCost AS UnitPrice * Quantity - Discount,  
    Tomorrow AS GETDATE()+1
)
GO
 
-- Insert a few rows
INSERT INTO Items VALUES (10,2,5.2)
INSERT INTO Items VALUES (20,1,2)
 
-- Show results
SELECT * FROM Items
 
UnitPrice  Quantity  Discount  TotalCost  Tomorrow
---------  --------  --------  ---------  -------------------
10.00      2         5.2       14.8       2012-03-09 18:50:20
20.00      1         2         18         2012-03-09 18:50:20

Create a temp table:

CREATE TABLE #customers (
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    CustomerCode NVARCHAR(20) NULL,
    IsActive BIT NOT NULL)
notes/sql/createtable.txt · Last modified: 2015/06/24 by admin