SQL 插入语句汇总
SqlServer
2019-07-01
735
0
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);
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;
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### 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';
EXEC sp_describe_first_result_set N'SELECT * FROM Sales.Customers;';
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 = @IDIF @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### 参考文档
01. 把存储过程结果集SELECT INTO到临时表 http://www.cnblogs.com/seusoftware/p/3222564.html