MS SQL 2008은 처리를 위해 저장된 절차에 데이터를 대량 업로드하는 데 유용한 기능인 TVP를 지원합니다.
사용자 정의 형식을 만드는 대신 기존 테이블 정의를 활용할 수 있습니까? 예를 들어, 다음과 같은 서명으로 저장된 절차를 만들 수 있습니까?
CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY
문서는 이것이 가능하지 않다고 제안하는 것 같습니다.
샘플 코드
/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/
USE AdventureWorks2008R2;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
/*
The following is not part of the original source code:
*/
CREATE TABLE LocationTable(
LocationName VARCHAR(50)
, CostRate INT );
GO
답변
아니요, 기존 테이블 정의를 활용할 수 없으므로 명시 적 유형을 정의해야합니다. 이 내용은 2007 년에 다시 요청되었으며 “수정하지 않음”으로 종료되었지만 여전히 유스 케이스와 이것이 비즈니스 생산성 향상에 도움이되는 의견을 남겨 주시기 바랍니다. 이 질문을 지적하여 이것을 시도하고 자동화하는 것이 얼마나 지루한 지 보여줄 수도 있습니다.
UserVoice의 항목 (이전 Connect # 294130)
예를 들어 간단한 정의를 위해 오늘 동적 으로이 작업을 수행 할 수 있습니다.
-- you would pass these two in as parameters of course:
DECLARE
@TableName SYSNAME = N'LocationTable',
@TypeName SYSNAME = N'LocationTypeTable';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9)
+ QUOTENAME(c.name) + ' '
+ s.name + CASE WHEN LOWER(s.name) LIKE '%char' THEN
'(' + CONVERT(VARCHAR(12), (c.max_length/
(CASE LOWER(LEFT(s.name, 1)) WHEN N'n' THEN 2 ELSE 1 END))) + ')'
ELSE '' END
-- need much more conditionals here for other data types
FROM sys.columns AS c
INNER JOIN sys.types AS s
ON c.system_type_id = s.system_type_id
AND c.user_type_id = s.user_type_id
WHERE c.[object_id] = OBJECT_ID(@TableName);
SELECT @sql = N'CREATE TYPE ' + @TypeName
+ ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
+ CHAR(13) + CHAR(10) + ');';
PRINT @sql;
-- EXEC sp_executesql @sql;
결과 :
CREATE TYPE LocationTypeTable AS TABLE
(
[LocationName] varchar(50),
[CostRate] int
);
면책 조항 : 이것은 MAX 유형, 숫자의 정밀도 및 스케일 등과 같은 다른 모든 종류를 다루지는 않습니다. 최종 솔루션은 모든 잠재적 열 정의를 설명하기 위해보다 강력해야하지만 시작해야합니다.
SQL Server 2012에는 sys.types 및 sys에 대한 모든 조건부 논리를 망칠 필요없이 기존 테이블 (또는 저장 프로 시저 또는 임시 쿼리)에서 열 메타 데이터를 훨씬 쉽게 파생시킬 수있는 새로운 DMV 및 저장 프로 시저가 있습니다. 열. 나는 12 월에 이러한 향상된 기능에 대해 간략하게 블로그 . 그것은 여전히 지루하지만, 위의 끔찍한 유지 불가능한 스파게티와 “[table x]의 사본으로”라고 말할 수있는 능력 사이의 어딘가에 있습니다 …
답변
기존 저장 테이블과 동일한 스키마를 가진 유형을 만들기 위해 다음 저장 프로 시저를 생성 하여이 문제를 해결했습니다.
Create PROCEDURE [dbo].[Sp_DefineTypeOutOfTableSchema]
@TableNames NVARCHAR(500)
AS
BEGIN
DECLARE @TableName NVARCHAR(100)
DECLARE @strSQL NVARCHAR(max)
DECLARE @strSQLCol NVARCHAR(1000)
DECLARE @ColName NVARCHAR(100)
DECLARE @ColDataTaype NVARCHAR(50)
DECLARE @ColDefault NVARCHAR(50)
DECLARE @ColIsNulable NVARCHAR(50)
DECLARE @ColCharMaxlen NVARCHAR(50)
DECLARE @ColNumPrec NVARCHAR(50)
DECLARE @ColNumScal NVARCHAR(50)
IF LEN(@TableNames) > 0 SET @TableNames = @TableNames + ','
WHILE LEN(@TableNames) > 0
BEGIN
SELECT @TableName = LTRIM(SUBSTRING(@TableNames, 1, CHARINDEX(',', @TableNames) - 1))
DECLARE schemaCur CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName
OPEN schemaCur
SELECT @strSQL=''
FETCH NEXT FROM schemaCur
INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColCharMaxlen,@ColNumPrec,@ColNumScal
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQLCol=''
SELECT @strSQLCol= '['+@ColName+'] '+'[' + @ColDataTaype +'] '
IF @ColDataTaype='nvarchar' or @ColDataTaype='char' or @ColDataTaype='varchar' or @ColDataTaype='vchar'
BEGIN
SELECT @strSQLCol=@strSQLCol+ '(' + @ColCharMaxlen +') '
END
ELSE IF @ColDataTaype='numeric' or @ColDataTaype='decimal'
BEGIN
SELECT @strSQLCol=@strSQLCol +'(' + @ColNumPrec +',' +@ColNumScal + ') '
END
IF @ColIsNulable='YES'
BEGIN
SELECT @strSQLCol=@strSQLCol+ 'NULL '
END
ELSE
BEGIN
SELECT @strSQLCol=@strSQLCol+ ' NOT NULL '
END
IF @ColDefault IS NOT NULL
BEGIN
SELECT @strSQLCol=@strSQLCol+ ' DEFAULT(' +@ColDefault + '),'
END
ELSE
BEGIN
SELECT @strSQLCol=@strSQLCol+ ' ,'
END
SELECT @strSQL=@strSQL+@strSQLCol
--print @strSQL
FETCH NEXT FROM schemaCur
INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColCharMaxlen,@ColNumPrec,@ColNumScal
END
CLOSE schemaCur
DEALLOCATE schemaCur
--print @strSQL
SELECT @strSQL=left( @strSQL, len(@strSQL)-1)
--print @strSQL
IF EXISTS (SELECT * FROM sys.types WHERE IS_TABLE_TYPE = 1 AND name = 't_' +@TableName)
BEGIN
EXEC('DROP TYPE t_' +@TableName )
END
SELECT @strSQL = 'CREATE TYPE t_' + @TableName + ' AS TABLE (' + @strSQL + ')'
--print @strSQL
EXEC (@strSQL)
SELECT @TableNames = SUBSTRING(@TableNames, CHARINDEX(',', @TableNames) + 1, LEN(@TableNames))
END
END이처럼 사용할 수 있습니다
Sp_DefineTypeOutOfTableSchema ‘Table1name, Table2name’실행
답변
Erland Sommarskog에는 TVP 사용 방법을 설명하는 광범위한 기사 가 있습니다.
한번보세요, 그만한 가치가 있습니다!
간단히 말해 Aaron Bertrand의 이전 답변 과 같이 기존 유형을 사용할 수 없습니다 . 그러나 적어도 그것은 대량 전송입니다 ..
답변
Jiten의 답변을 기반으로 장소의 일부 논리를 단순화하고 identity열을 설명합니다 ( sys.대신 테이블 사용 SCHEMEA)
CREATE PROCEDURE [dbo].[usp_DefineTypeFromTable]
@TableNames NVARCHAR(500)
AS
BEGIN
DECLARE @TableName NVARCHAR(100)
DECLARE @strSQL NVARCHAR(max)
DECLARE @strSQLCol NVARCHAR(1000)
DECLARE @ColName NVARCHAR(100)
DECLARE @ColDataTaype NVARCHAR(50)
DECLARE @ColDefault NVARCHAR(50)
DECLARE @ColIsNulable NVARCHAR(50)
DECLARE @ColFirst NVARCHAR(50)
DECLARE @ColSecond NVARCHAR(50)
DECLARE @ColID NVARCHAR(50)
DECLARE @ColCompute NVARCHAR(50)
IF LEN(@TableNames) > 0 SET @TableNames = @TableNames + ','
WHILE LEN(@TableNames) > 0
BEGIN
SELECT @TableName = TRIM(LEFT(@TableNames, CHARINDEX(',', @TableNames) - 1))
DECLARE schemaCur CURSOR FOR
SELECT
c.name as column_name,
t.name as [type_name],
c.is_nullable,
convert(nvarchar(4000), object_definition(ColumnProperty(c.object_id, c.name, 'default'))) as column_default,
CASE
WHEN c.collation_name IS NOT NULL THEN c.max_length
WHEN t.name like 'datetime%' THEN c.scale
WHEN c.scale = 0 THEN NULL
ELSE c.precision
END as firstValue,
CASE
WHEN (c.scale = 0 or t.name like 'datetime%') THEN NULL
ELSE c.scale
END as secondValue,
c.is_identity, -- would be best to know seed,increment
c.is_computed -- should really look up col definition. `convert(nvarchar(4000), object_definition(ColumnProperty(c.object_id, c.name, 'computed')))` as computed ?
FROM sys.columns as c join
sys.all_objects as o
on c.object_id=o.object_id join
sys.types as t
on c.user_type_id=t.user_type_id
WHERE
o.type in ('U','V','TF','IF','TT') and --'S' to include built-in tables/types
o.name = @TableName
ORDER BY o.name, c.column_id
OPEN schemaCur
SELECT @strSQL=''
FETCH NEXT FROM schemaCur
INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColFirst,@ColSecond,@ColID,@ColCompute
WHILE @@FETCH_STATUS = 0 BEGIN
-- SELECT @strSQLCol=''
SELECT @strSQLCol= '['+@ColName+'] '+'[' + @ColDataTaype +'] '
IF @ColSecond is NULL
BEGIN
IF @ColFirst is not NULL SELECT @strSQLCol += '(' + @ColFirst + ') '
END
ELSE SELECT @strSQLCol += '(' + @ColFirst +',' +@ColSecond + ') '
IF @ColID>0 SELECT @strSQLCol += ' IDENTITY(1,1)'
IF @ColIsNulable>0 SELECT @strSQLCol += 'NULL'
ELSE SELECT @strSQLCol += ' NOT NULL'
IF @ColDefault IS NOT NULL SELECT @strSQLCol += ' DEFAULT(' +@ColDefault + '),'
ELSE SELECT @strSQLCol += ','
SELECT @strSQL += @strSQLCol
--print @strSQL
FETCH NEXT FROM schemaCur
INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColFirst,@ColSecond,@ColID,@ColCompute
END
CLOSE schemaCur
DEALLOCATE schemaCur
SELECT @strSQL=left(@strSQL, len(@strSQL)-1)
IF EXISTS (SELECT * FROM sys.types WHERE IS_TABLE_TYPE = 1 AND name = 'tt_' +@TableName)
BEGIN
EXEC('DROP TYPE tt_' +@TableName )
END
SELECT @strSQL = 'CREATE TYPE tt_' + @TableName + ' AS TABLE (' + @strSQL + ')'
-- print @strSQL
EXEC (@strSQL)
SELECT @TableNames = SUBSTRING(@TableNames, CHARINDEX(',', @TableNames) + 1, LEN(@TableNames))
END
END