-- single row INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES(2, 19, '20120620', N'USA', 30.00);

-- relying on defaults INSERT INTO Sales.MyOrders(custid, empid, shipcountry, freight) VALUES(3, 11, N'USA', 10.00);
INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES(3, 17, DEFAULT, N'USA', 30.00);
-- multiple rows INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES (2, 11, '20120620', N'USA', 50.00), (5, 13, '20120620', N'USA', 40.00), (7, 17, '20120620', N'USA', 45.00);

SET IDENTITY_INSERT <table> ON;

SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight) SELECT orderid, custid, empid, orderdate, shipcountry, freight FROM Sales.Orders WHERE shipcountry = N'Norway';
SET IDENTITY_INSERT Sales.MyOrders OFF;

INSERT EXEC

-- create procedure
IF OBJECT_ID(N'Sales.OrdersForCountry', N'P') IS NOT NULL
  DROP PROC Sales.OrdersForCountry;
GO

CREATE PROC Sales.OrdersForCountry
  @country AS NVARCHAR(15)
AS

SELECT orderid, custid, empid, orderdate, shipcountry, freight
FROM Sales.Orders
WHERE shipcountry = @country;
GO

-- insert the result of the procedure
SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)
  EXEC Sales.OrdersForCountry
    @country = N'Portugal';

SET IDENTITY_INSERT Sales.MyOrders OFF;

CREATE TABLE test_dbcc
    (
      TraceFlag VARCHAR(100) ,
      Status TINYINT ,
      Global TINYINT ,
      Session TINYINT
    )

INSERT  INTO test_dbcc
        EXEC ( 'DBCC TRACESTATUS'
            )

exec sp_configure 'show advanced options',1
RECONFIGURE
GO
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO

--使用Windows认证
SELECT  *
INTO    #JobInfo_S1
FROM    OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes',
                   'exec msdb.dbo.sp_help_job')

--使用SQL Server认证
SELECT  *
INTO    #JobInfo_S2
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'exec msdb.dbo.sp_help_job')

--dbcc不能直接运行
SELECT  a.*
INTO    #t
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'dbcc log(''master'',3)') AS a

--可以变通一下
SELECT  a.*
INTO    #t
FROM    OPENROWSET('SQLOLEDB', '127.0.0.1'; 'sa'; 'sa_password',
                   'exec(''DBCC LOG(''''master'''',3)'')') AS a

-- simple SELECT INTO
IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders;

SELECT orderid, custid, orderdate, shipcountry, freight
INTO Sales.MyOrders
FROM Sales.Orders
WHERE shipcountry = N'Norway';

EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';

select * into #temp from sysobjects where 1=2

CREATE PROCEDURE [dbo].[Students_Insert] ( @ID INT , @LASTNAME VARCHAR(50) , @FIRSTNAME VARCHAR(50) , @STATE VARCHAR(50) , @PHONE VARCHAR(50) , @EMAIL VARCHAR(50) , @GRADYEAR INT , @GPA DECIMAL(20, 10) , @PROGRAM VARCHAR(50) , @NEWSLETTER BIT ) AS BEGIN --Check to make sure the ID does not already exist --If it does, return error DECLARE @existing AS INT = 0 SELECT  @existing = COUNT(ID) FROM    Students WHERE   ID = @ID

    IF @existing > 0
        BEGIN
            RAISERROR ('ID already exists', 1, 1)
            RETURN 0
        END
--Format GPA as 2 decimal places
    DECLARE @TwoDecimalGPA AS DECIMAL(3, 2)
    SELECT  @TwoDecimalGPA = CAST(@GPA AS NUMERIC(3, 2))
--Make sure GPA is within range
    IF ( ( @TwoDecimalGPA > 4 )
         OR ( @TwoDecimalGPA < 0 )
       )
        BEGIN
            RAISERROR ('GPA value is invalid', 1, 1)
            RETURN 0
        END     
--Attempt insert
    INSERT  INTO [dbo].[Students]
            ( [ID] ,
              [LASTNAME] ,
              [FIRSTNAME] ,
              [STATE] ,
              [PHONE] ,
              [EMAIL] ,
              [GRADYEAR] ,
              [GPA] ,
              [PROGRAM] ,
              [NEWSLETTER]
            )
    VALUES  ( @ID ,
              @LASTNAME ,
              @FIRSTNAME ,
              @STATE ,
              @PHONE ,
              @EMAIL ,
              @GRADYEAR ,
              @TwoDecimalGPA ,
              @PROGRAM ,
              @NEWSLETTER
            )
       --check to see if insert occured 
       --and return status
    IF @@ROWCOUNT = 1
        RETURN 1
    ELSE
        RETURN 0
END

GO

-----SQL SERVER中直接循环写入数据-----
DECLARE @i INT
SET @i = 1
WHILE @i < 30
    BEGIN
        INSERT  INTO test
                ( userid )
        VALUES  ( @i )
        SET @i = @i + 1
    END
--案例：
--有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:
--    Name     score
--    Zhangshan   80
--    Lishi       59
--    Wangwu      50
--    Songquan    69

WHILE ( ( SELECT    MIN(score)
          FROM      tb_table
        ) < 60 )
    BEGIN
        UPDATE  tb_table
        SET     score = score * 1.01
        WHERE   score < 60
        IF ( SELECT MIN(score)
             FROM   tb_table
           ) > 60
            BREAK
        ELSE
            CONTINUE
    END