sql插数据语句_sql语句批量添加数据

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

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

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