This website requires JavaScript.

SQL 插入语句汇总

INSERT VALUES

插入一行或多行到目标表中

-- 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);

注:如果要更新自增字段需要把IDENTITY_INSERT选项开启,用完后记得关闭
SET IDENTITY_INSERT <table> ON;

INSERT SELECT

把select 语句的查询结果插入到表中,这个中方法要比上面的INSERT VALUES 效率高

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;
对于动态SQL,或者类似DBCC这种非常规的SQL语句,都可以通过这种方式来保存结果集。
CREATE TABLE test_dbcc
    (
      TraceFlag VARCHAR(100) ,
      Status TINYINT ,
      Global TINYINT ,
      Session TINYINT
    )

INSERT  INTO test_dbcc
        EXEC ( 'DBCC TRACESTATUS'
            )
注:不能嵌套使用insert exec语句 ,可以用以下方式变动(这里直接拷贝参考文章中的内容。我没试验过)
1.首先到打开服务器选项Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
RECONFIGURE
GO
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO
2\. 通过OPENROWSET连接到本机,运行存储过程,取得结果集
--使用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')
这样的写法,既免去了手动建表的麻烦,也可以避免insert exec 无法嵌套的问题。几乎所有SQL语句都可以使用。
--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
### SELECT INTO
该语句不需要我们事先建立目标表,而是直接拷贝数据源或者查询结果数据定义:比如列名,类型,是否为空,自增等来建立目标表。
-- 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';
通过以下存储可以查看查询返回字段的结构,注意name,system_type_name,is_nullable.
EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';
利用select into 生成一个空表
select * into #temp from sysobjects where 1=2

实际插入案例

1) 学生表插入

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 &gt; 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 &gt; 4 )
         OR ( @TwoDecimalGPA &lt; 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

2) 循环插入
-----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
### 参考文档
The Data Loading Performance Guide [http://msdn.microsoft.com/en-us/library/dd425070.aspx.](http://msdn.microsoft.com/en-us/library/dd425070.aspx. "http://msdn.microsoft.com/en-us/library/dd425070.aspx.")

01. 把存储过程结果集SELECT INTO到临时表 http://www.cnblogs.com/seusoftware/p/3222564.html

0条评论
avatar