SQL Server通过自定义表类型批量存数据

1、使用SqlServer新建TestData表，所有字段均采用varchar类型；

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TestData') AND type = N'U') 
begin
    CREATE TABLE [dbo].TestData
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Parameter000] varchar(50) NULL,
        [Parameter001] varchar(50) NULL,
        [Parameter002] varchar(50) NULL,
        --......
        [Parameter999] varchar(50) NULL
    ) ON [PRIMARY]
END
GO

2、新建一张DataConfig表，在首次使用时，给通讯协议绑定通道数据存放位置，绑定信息放在DataConfig表里；

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.DataConfig') AND type = N'U') 
begin
    CREATE TABLE [dbo].DataConfig
    (
        [AgreementName] varchar(50) NULL,
        [SignalName] varchar(50) NULL,
        [ParameterName] varchar(50) NULL
    ) ON [PRIMARY]
END
GO

--AgreementName： 存放通讯协议的名称；
--SignalName：存放协议里每个信号的特征；
--ParameterName：存放TestData里的字段名；
--每个信号占用一个TestData的字段，ParameterName不得重复使用同一个字段名；

3、新建存储过程和用户自定义表类型，表类型必须与TestData表一致

CREATE TYPE TestDataType AS TABLE(
	Parameter000 Varchar(50),
	Parameter001 Varchar(50),
	Parameter002 Varchar(50),
    --......
	Parameter999 Varchar(50)
)


CREATE　PROC　[dbo].[SaveTestDataType]
@TestData [TestDataType] READONLY
AS
INSERT　TestData　SELECT　*　FROM　@TestData
GO

在程序中根据TestDataType创建一个数据表格（DataTable），将实验过程中的所有数据缓存至此表，直至测试结束后，将此表格当作参数传入存储过程，通过存储过程将数据保存到TestData；

4、当TestData的字段不够用时，可通过以下方法在程序中自动添加字段；

--查询TestData表的字段
Select COLUMN_NAME as Name from information_schema.COLUMNS where TABLE_NAME='TestDataTable' AND COLUMN_NAME !='ID' AND COLUMN_NAME !='TestID' AND COLUMN_NAME !='TestTime'

在程序中使用字符串截取方法把所有字段名称里的（Parameter）去掉，把字段名全部转为数字，找出最大的数字+1然后转为字符串，在前面拼接上（Parameter）作为新字段的名称；

5、TestData表插入新字段

if NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TestData' AND COLUMN_NAME = 'Parameter000')
begin
ALTER TABLE TestData ADD Parameter000 Varchar(50)
end
GO

6、通过以下SQL语句修改TestDataType，先重命名原来的表类型，再新建新的表类型；

--判断表类型是否存在，存在就重命名
IF EXISTS (SELECT * FROM sys.types WHERE is_user_defined = 1 AND name = '表类型名称')
BEGIN
 EXEC sys.sp_rename '原表类型名称', '新表类型名称'
END
GO

--使用原名称新建表类型
CREATE TYPE 原表类型名称 AS TABLE(
	Parameter000 Varchar(50),
	Parameter001 Varchar(50),
	Parameter002 Varchar(50),
    --......
	Parameter00n Varchar(50)
)


IF EXISTS (SELECT * FROM sys.types WHERE is_user_defined = 1 AND name = '表类型名称')
    PRINT '表类型存在'
ELSE
    PRINT '表类型不存在'

7、更新表类型引用对象的元数据信息，避免因表类型变更导致的错误；

--定义名称参数
DECLARE @Name NVARCHAR(500)
--定义游标，用于遍历查询结果
DECLARE REF_CURSOR CURSOR FOR
--查询TestDataType的引用对象
SELECT referencing_schema_name + '.' + referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.TestDataTableType', 'TYPE')
--打开游标
OPEN REF_CURSOR
--通过游标遍历查询结果
FETCH NEXT FROM REF_CURSOR INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
	--刷新引用TestDataType的对象的元数据信息
	EXEC sys.sp_refreshsqlmodule @name = @Name
	FETCH NEXT FROM REF_CURSOR INTO @Name
END
--关闭游标
CLOSE REF_CURSOR
--删除游标引用
DEALLOCATE REF_CURSOR

8、删除被重名的那个表类型

--判断表类型是否存在，存在就删除
IF EXISTS (SELECT * FROM sys.types WHERE is_user_defined = 1 AND name = '重命名后的表类型名称')
BEGIN
 DROP TYPE 重命名后的表类型名称
END
GO

9、授予公共角色对TestDataTableType 表类型的执行权限；

GRANT EXECUTE ON TYPE::dbo.TestDataTableType TO public