SQL table-Valued parameters BULK Insert

--Use table-valued parameters to bulk insert data into another data table

Use Df17datapro
--Create and use table-valued parameter steps
/*
1. Create a table type and define the table structure.
For information about how to create a SQL Server type, see user-defined table types. For more information about how to define the table structure, see CREATE table (Transact-SQL).
2. Declare a routine with a table-type parameter. For more information about SQL Server routines, see Create PROCEDURE (Transact-SQL) and create FUNCTION (Transact-SQL).
3. Declare the table type variable and reference the table type. For information about how to declare variables, see DECLARE @local_variable (Transact-SQL).
4. Use the INSERT statement to populate the table variable. For more information about how to insert data, see Add rows by using Insert and SELECT.
5. After creating and populating a table variable, you can pass the variable to the routine.
*/

--Concrete implementation
-------------------------------------------------------------
--1. Creating a table-valued parameter type Bulkvalue

IF EXISTS(SELECT *  fromSYS. TYPES StJOINSYS. SCHEMAS SS onSt. schema_id=SS. schema_idWHERESt.name=N'[Bulkvalue]'  andSs.name=N'dbo')DROPTYPE[dbo].[Bulkvalue]GOCREATETYPE Bulkvalue as TABLE(IdflagINT,--primary Key IDRecvtimeFLOAT  not NULL,--receive time, there is no time the same dataAaINT  not NULLCaINT NULL, FlightidVarchar(Ten)NULL,--Flight Number)

-------------------------------------------------------------
--(2) Declaring a stored procedure with a table type parameter
-------------------------------------------------------------

IF exists(SELECT *  fromSYS. ProceduresWHERE object_id = object_id(N'[dbo]. [Insertdbbulkproc]'))DROP PROC [dbo]. InsertdbbulkprocGo --Create a procedure to get the table-valued parameter dataCREATE PROCEDUREInsertdbbulkproc@TVPBulkvalue READONLY as SETNOCOUNT onINSERT  intobasicmsg (Recvtime,aa,ca,flightid)SELECTRecvtime,aa,ca,flightid from @TVP;GO

-------------------------------------------------------------
--(3) Declaring a variable to refer to the type
-------------------------------------------------------------

DECLARE @LocationTVP  as Bulkvalue;

-------------------------------------------------------------
--(4) adding data to variables
-------------------------------------------------------------

INSERT  into @LocationTVP (Recvtime,aa,ca,flightid) SELECT Recvtime,aa,ca,flightid  from [dbo]. [BasicMsg20170518];

Where BasicMsg20170518 is the source data table.

-------------------------------------------------------------
--(5) passing the data of the table variable to the stored procedure
-------------------------------------------------------------

EXEC @LocationTVP ; GO

SQL table-Valued parameters BULK Insert