How to create table in SQL Server

sql2008

USE Arm_DB
Go

 IF (OBJECT_ID('TBL_Product','U') IS NOT NULL)
   DROP TABLE TBL_Product

Go

CREATE TABLE TBL_Product
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
,Price DECIMAL(10,2)
,[Desc] NVARCHAR(MAX)
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,MOdifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TBL_Product PRIMARY KEY (ID)
)

GO 

 IF (OBJECT_ID('TBL_Sales','U')IS NOT NULL )
    DROP TABLE TBL_Sales

CREATE TABLE TBL_Sales
(
ID INT IDENTITY(1,1)
,ProductID INT 
,Quantity INT
,SalesDate DATETIME
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,ModifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TB_Sales PRIMARY KEY (ID)
CONSTRAINT FK_TBL_Sales_TBL_Product 
     FOREIGN KEY (ProductID) REFERENCES dbo.TBL_Product(ID)
)

GO

INSERT dbo.TBL_Product   ( NAME ,Price )
SELECT 'Book',32.4
UNION ALL
SELECT 'Pen',5.2
UNION ALL
SELECT 'Notebook',8.7

Go

INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
SELECT 1,4,'2013-Apr-10'
UNION ALL
SELECT 1,3,'2013-Apr-11'
UNION ALL
SELECT 1,8,'2013-Apr-13'
UNION ALL
SELECT 1,1,'2013-Apr-18'
UNION ALL
SELECT 2,4,'2013-Apr-10'
UNION ALL
SELECT 2,3,'2013-Apr-11'
UNION ALL
SELECT 2,8,'2013-Apr-13'
UNION ALL
SELECT 2,1,'2013-Apr-18'
UNION ALL
SELECT 3,4,'2013-Apr-10'
UNION ALL
SELECT 3,5,'2013-Apr-11'
UNION ALL
SELECT 3,3,'2013-Apr-13'
UNION ALL
SELECT 3,9,'2013-Apr-18'

SELECT * FROM dbo.TBL_Product

SELECT * FROM dbo.TBL_Sales


Categories: SQL Server, SQL Server Development

Tags: , ,

7 replies

  1. What is the best data type for storing passwords???

  2. You can also tailor your INSERT as follows:

    INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
    VALUES (1,4,’2013-Apr-10′), (1,3,’2013-Apr-11′), (1,8,’2013-Apr-13′), etc., which is more succinct.

Trackbacks

  1. Import Export wizard in SQL Server step by step | Coding Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: