SQL表值参数批量插入

-------------------------------------------------------------
--(2)声明具有表类型参数的存储过程
-------------------------------------------------------------

IF exists (SELECT * FROM SYS.PROCEDURES WHERE OBJECT_ID = OBJECT_ID(N‘[dbo].[InsertDBBulkProc]‘))
DROP PROC [dbo].InsertDBBulkProc
go 
-- 创建一个过程来获得该表值参数数据 
CREATE PROCEDURE InsertDBBulkProc 
@TVP BulkValue READONLY
AS 
SET NOCOUNT ON
INSERT INTO BasicMsg (RecvTime,AA,CA,FlightID )
SELECT RecvTime,AA,CA,FlightID
FROM @TVP;
GO

-------------------------------------------------------------
--(3) 声明一个变量来引用该类型
-------------------------------------------------------------

DECLARE @LocationTVP 
AS BulkValue ;

-------------------------------------------------------------
--(4) 将数据加入变量中
-------------------------------------------------------------

INSERT INTO @LocationTVP (RecvTime,AA,CA,FlightID)
SELECT RecvTime,AA,CA,FlightID
FROM 
[dbo].[BasicMsg20170518];

其中，BasicMsg20170518为源数据表。

-------------------------------------------------------------
--(5) 把表变量的数据传递给存储过程
-------------------------------------------------------------

EXEC InsertDBBulkProc @LocationTVP;
GO

SQL表值参数批量插入

标签：sts   weight   exe   color   basic   存在   rom   roc   步骤