Sql server® 2012 t-sql fundamentals

292  

Microsoft SQL Server 2012 T-SQL Fundamentals

1-3

The following code first ensures that the session is connected to the TSQL2012 database, then it drops 

the dbo.Orders table if it already exists, and then it uses the SELECT INTO statement to create a new 

dbo.Orders table and populate it with orders from the Sales.Orders table placed in the years 2006 

through 2008.

USE TSQL2012; 

 

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; 

 

SELECT * 

INTO dbo.Orders 

FROM Sales.Orders 

WHERE orderdate >= '20060101' 

  AND orderdate < '20090101';

In SQL Database, you use CREATE TABLE and INSERT SELECT statements instead.

CREATE TABLE dbo.Orders 

( 

  orderid        INT          NOT NULL, 

  custid         INT          NULL, 

  empid          INT          NOT NULL, 

  orderdate      DATETIME     NOT NULL, 

  requireddate   DATETIME     NOT NULL, 

  shippeddate    DATETIME     NULL, 

  shipperid      INT          NOT NULL, 

  freight        MONEY        NOT NULL, 

  shipname       NVARCHAR(40) NOT NULL, 

  shipaddress    NVARCHAR(60) NOT NULL, 

  shipcity       NVARCHAR(15) NOT NULL, 

  shipregion     NVARCHAR(15) NULL, 

  shippostalcode NVARCHAR(10) NULL, 

  shipcountry    NVARCHAR(15) NOT NULL, 

  CONSTRAINT PK_Orders PRIMARY KEY(orderid) 

); 

 

INSERT INTO dbo.Orders 

  (orderid, custid, empid, orderdate, requireddate, shippeddate, 

   shipperid, freight, shipname, shipaddress, shipcity, shipregion, 

   shippostalcode, shipcountry) 

SELECT 

  orderid, custid, empid, orderdate, requireddate, shippeddate, 

  shipperid, freight, shipname, shipaddress, shipcity, shipregion, 

  shippostalcode, shipcountry 

FROM Sales.Orders 

WHERE orderdate >= '20060101' 

  AND orderdate < '20090101';

www.it-ebooks.info

---