[MSSQL] 엑셀 데이터 테이블레이아웃 => CREATE TABLE SQL문

[MSSQL] 엑셀 데이터 테이블레이아웃 => CREATE TABLE SQL문

 

 

- 엑셀로 정리한 테이블 레이아웃을 CREATE TABLE SQL문으로 변경

 

- 엑셀 테이블 레이아웃 형태

 

 

- 순서: 엑셀 => DTD =>변환 SQL문 실행

 

 

<SQL문>

 

- SQL문에서 변경값은 위 엑셀 테이블 레이아웃 형태의 경우값으로 설정

------------------------------------------------------------------------------------------- -- -- 엑셀 테이블 레이아웃 => CREATE TABLE SQL문으로 변경하기 -- ------------------------------------------------------------------------------------------- DECLARE @wSQL VARCHAR(8000) DECLARE @wIndex INT DECLARE @wMax INT DECLARE @wTblName VARCHAR(30) DECLARE @wTmpTblName VARCHAR(30) DECLARE @wFieldName VARCHAR(50) DECLARE @wFieldType VARCHAR(50) DECLARE @wSize VARCHAR(50) DECLARE @wNullAble VARCHAR(10) DECLARE @wFieldTypeDes VARCHAR(100) DECLARE @wDescription VARCHAR(100) SELECT @wSQL = '',@wIndex = 1,@wMax = 0 -------------------------------------------------------------------------------------------- -- -- 이부분은 사용자에 맞게 수정 -- @wTblName : 만들고자 하는 테이블명, @wTmpTblName : 엑셀 가져오기 한 테이블명 -- @wFieldName : 필드나열 컬럼명, @wFieldType : 타입나열 컬럼명, @wSize : 길이나열 컬럼명 -- @wNullAble : Null유무 나열컬럼명, @wDescription : 컬럼 설명 -------------------------------------------------------------------------------------------- SELECT @wTblName = 'Tbl명',@wTmpTblName = 'Sheet1$',@wFieldName = '필드명', @wFieldType = 'type',@wSize = '길이',@wNullAble = 'Null유무', @wDescription = '설명' -------------------------------------------------------------------------------------------- -- -- 이부분은 사용자에 맞게 수정 끝 -- -------------------------------------------------------------------------------------------- DROP TABLE #TEMP1 CREATE TABLE #TEMP1 ( Seq INT IDENTITY(1,1), FieldName VARCHAR(50), Description VARCHAR(1000), FieldType VARCHAR(50), Size VARCHAR(50), NullAble VARCHAR(10) ) SELECT @wSQL = ' INSERT INTO #TEMP1( FieldName,Description,FieldType,Size,NullAble )' + ' SELECT ' + @wFieldName + ',' + @wDescription + ',' + @wFieldType + ',' + @wSize + ',' + @wNullAble + ' FROM ' + @wTmpTblName EXEC(@wSQL) SELECT @wMax = COUNT(*) FROM #TEMP1 SELECT @wSQL = ' CREATE TABLE ' + @wTblName + CHAR(13) SELECT @wSQL = @wSQL + ' ( ' + CHAR(13) --SELECT * FROM #TEMP1 WHILE ( @wIndex <= @wMax ) BEGIN SELECT @wFieldTypeDes = '' SELECT @wFieldName = FieldName,@wFieldType = FieldType,@wSize = ISNULL(Size,''),@wNullAble = ISNULL(NullAble,''),@wDescription = ISNULL(Description,'') FROM #TEMP1 WHERE Seq = @wIndex SELECT @wFieldTypeDes = CASE WHEN UPPER(@wFieldType) = 'VARCHAR' THEN UPPER(@wFieldType) + '(' + CASE WHEN @wSize = '' THEN 'MAX' ELSE @wSize END + ')' WHEN UPPER(@wFieldType) = 'NVARCHAR' THEN UPPER(@wFieldType) + '(' + CASE WHEN @wSize = '' THEN 'MAX' ELSE @wSize END + ')' ELSE UPPER(@wFieldType) END SELECT @wSQL = @wSQL + ' ' + @wFieldName + ' ' + @wFieldTypeDes + ' ' + @wNullAble + CASE WHEN @wIndex = @wMax THEN '' ELSE ',' END + CHAR(13) SELECT @wIndex = @wIndex + 1 END SELECT @wSQL = @wSQL + ' ) ' +CHAR(13)+CHAR(10) +CHAR(13)+CHAR(10) ----------------------------------------------------- -- 설명 넣기 ----------------------------------------------------- SELECT @wIndex = 1 WHILE ( @wIndex <= @wMax ) BEGIN SELECT @wFieldTypeDes = '' SELECT @wFieldName = LTRIM(RTRIM(FieldName)),@wDescription = ISNULL(Description,'') FROM #TEMP1 WHERE Seq = @wIndex SELECT @wSQL = @wSQL + ' EXEC sp_addextendedproperty @name = N''MS_Description'', @value = ''' + @wDescription + ''',' +CHAR(13)+CHAR(10) + ' @level0type = N''Schema'', @level0name = ''dbo'',' +CHAR(13)+CHAR(10) + ' @level1type = N''Table'', @level1name = ''' + @wTblName + ''',' +CHAR(13)+CHAR(10) + ' @level2type = N''Column'', @level2name = ''' + @wFieldName + '''' +CHAR(13)+CHAR(10) +CHAR(13)+CHAR(10) +CHAR(13)+CHAR(10) SELECT @wIndex = @wIndex + 1 END PRINT(@wSQL)