꿈꾸는 개발자, DBA 커뮤니티 구루비

T-SQL 에서 Loop 문을 이용한 CREATE TABLE 구문 생성

by 배움 [SQLServer] T-SQL Loop [2022.08.24 00:53:21]

안녕하세요.

SQL Server 에 있는 많은 테이블을 Oracle 에 생성해야 하는데 T-SQL 초보라 헤매다가 질문 드립니다.
지금까지 제가 10개 정도의 SQL Server에 있는 테이블을 하나 하나 create table 로 오라클에 만들었습니다만 앞으로 만들어야 할 테이블의 숫자가 훨씬 많아진 상황입니다.
그래서 SQL Server 에서 T-SQL 에서 Loop 를 돌려서 CREATE TABLE 구문을 만들수 있는지 궁금합니다.

즉, SQL Server 에 만일 10개의 table 이 있고 그 각각의 테이블에 서로 다른 개수의 columns 가 있을 때, objects list 의 table 개수만큼 loop 를 돌리고 그 loop 안에서 columns 의 data type을 오라클에 맞게 변경하는 작업을 고민하고 있습니다.

이를 위해 제가 고려하는 사항은 다음과 같은데요.

1. SQL Server 에서 T-SQL 을 실행해서 Create Table 하는 DDL 을 만든다.

2. Create Table 을 할 때 SQL Server 에 있는 소스 테이블의 이름으로 Table Name 을 만든다.

(예) CREATE TABLE SRC_TABLE_NAME

3. T-SQL 에 있는 BEGIN ... END 블럭 내에서 Oracle에 맞게 각 Column의 Data Type 변경 되어야 한다. (Data Type Conversion)

4. 이 때 (2번을 수행할 때) 1개의 Table에 있는 column 의 개수만큼 WHILE 이나 FOR 문으로 Loop 를 돌며 Oracle 의 Data Type으로 conversion 한다.

5. 최종적으로 테이블 1개마다 CREATE TABLE 을 위한 DDL 을 만든다.

제가 해 본 것은 아래와 같습니다만 너무 허접하고 아직 초기 단계라서 어떻게 해야 할 지 모르겠어요.

우선 소스 테이블이 SQL Server 에 있기 때문에 검색해서 모든 테이블 list 를 뽑았습니다.

SELECT NAME AS ObjName
    ,schema_name(schema_id) AS SchemaName
    ,type AS ObjType
    ,type_desc AS ObjTypeDesc
    ,create_date
    ,modify_date
    FROM sys.objects
WHERE type_desc = 'USER_TABLE' AND schema_name(schema_id) = 'dbo'
ORDER BY ObjName ASC, ObjType ASC

그런데 테이블 하나마다 Column 개수를 세서 CREATE TABLE statement 를 만들려니 그 테이블 리스트를 제공하는 sys.objects 를 아래의 제가 테스트 중인 SQL 과 PL/SQL 에 어떻게 넣어야 할 지 모르겠습니다.

여기서 질문입니다. 위의 SQL 에서 sys.objects 와 아래 SQL 에서 sys.tables 은 시스템에서 같은 objects 를 가리키는 걸까요?

어떤 조건을 넣어야 1개의 table 에 대해 loop 를 도는지 정말 찾기가 힘드네요. MS 설명을 읽으면서

sys.objects 도 넣어보고 sys.types 도 넣어보고 sys.tables 도 사용하고 있는데 이제 처음이라 무슨 값을 사용해서 1개의 table 내에서 그 테이블이 갖고 있는 column 개수만큼 Loop 를 돌고,

1개 테이블에 대한 처리가 끝나면 (DDL 생성이 완료되면) 그 다음 테이블에 대해 처리할 수 있을까요?

SELECT
CONCAT('CREATE TABLE ', REPLACE(UPPER(tab.name), '_',''), '(', 
        REPLACE(UPPER(col.name), '_',''), 
        CASE WHEN UPPER(t.name) = 'MONEY' THEN '    NUMBER(19,4)'
             WHEN UPPER(t.name) = 'REAL' THEN ' FLOAT(23)'
             WHEN UPPER(t.name) = 'FLOAT' THEN '    FLOAT(49)'
             WHEN UPPER(t.name) = 'NVARCHAR' THEN ' NCHAR'
             ELSE ' ' + UPPER(t.name)
             END, ');') 
         AS ORA_CONVERSION
  FROM sys.tables tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
;

최종적으로 나오길 바라는 DDLs 는

1 2	`CREATE` `TABLE` `TABLE01_NAME(COLUMN1 DATATYPE,` `COLUMN2 DATATYPE, COLUMN3 DATATYPE,...,COLUMNX DATATYPE);`

CREATE TABLE TABLE02_NAME(COLUMN1 DATATYPE,
COLUMN2 DATATYPE, COLUMN3 DATATYPE,...,COLUMNX DATATYPE);
CREATE TABLE TABLE03_NAME(COLUMN1 DATATYPE,
COLUMN2 DATATYPE, COLUMN3 DATATYPE,...,COLUMNX DATATYPE);
CREATE TABLE TABLE04_NAME(COLUMN1 DATATYPE,
COLUMN2 DATATYPE, COLUMN3 DATATYPE,...,COLUMNX DATATYPE);
...

T-SQL 은 지금 이렇게 우선 시작해 보고 있습니다. 하지만 아래와 같이 하면 모든 tables 의 모든 columns 를 읽어요.

그래서 하나의 테이블마다 변환을 마치는 것으로 어떻게 코드를 만들 수 있는지 궁금합니다.

정말 매뉴얼 작업으로 많은 테이블을 만들어야 해서 고민이 많습니다. 도와주시면 정말 감사합겠습니다.

DECLARE @colCount INT;
-- 하나의 테이블에서 최대 column 개수가 될 때까지 loop를 돌고 다음 테이블로 넘어갈 수 있을까요? 
-- WHILE @colCount > 0  
    BEGIN
        SELECT @colCount = COUNT(col.name)
        FROM sys.tables tab inner join sys.columns as col
        on tab.object_id = col.object_id
        left join sys.types as t
        on col.user_type_id = t.user_type_id
        PRINT @colCount 
        --print 위치에 CONCAT 을 넣어서 column 이름과 datatype을 계속 이어갈 수 있을까요?
    END
--  SET @colCount = @colCount - 1
--END;

by 배움 [2022.08.24 12:48:12]

DECLARE @TableCnt INT  
DECLARE @ColumnCnt INT
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
 
SET @TableName = ''
SET @ColumnCnt = 0
SET @TableCnt =  
(
    SELECT count(tab.name)
    FROM  sys.tables tab
)
 
 
WHILE (@TableCnt > 0)
BEGIN
SET @ColumnName = ''
SET @TableName = 
    (
        SELECT tab.name
        FROM   sys.tables tab
        --WHERE  테이블을 하나씩 선택하는 조건은 무엇?
 
    )
 
    -- 하나의 테이블 내에 몇 개의 columns 가 있나
    SET @ColumnCnt = 
    (
        SELECT COUNT(COL.name)
        FROM sys.columns COL
            INNER JOIN sys.tables TAB
            On COL.object_id = TAB.object_id
            left join sys.types as col_type
            on col.user_type_id = col_type.user_type_id
    )
 
            WHILE (@ColumnCnt > 0)
            BEGIN
                SELECT
                        CONCAT('CREATE TABLE ', REPLACE(UPPER(@TableName), '_',''), '(', 
                        REPLACE(UPPER(COL.name), '_',''), CASE WHEN UPPER(col_type.name) = 'MONEY' THEN '   NUMBER(19,4)'
                                                            WHEN UPPER(col_type.name) = 'REAL' THEN '   FLOAT(23)'
                                                            WHEN UPPER(col_type.name) = 'FLOAT' THEN '  FLOAT(49)'
                                                            WHEN UPPER(col_type.name) = 'NVARCHAR' THEN '   NCHAR'
                                                            ELSE '  ' + UPPER(col_type.name)
                                                            END, ');') 
                                                            AS ORA_CONVERSION
                FROM sys.columns COL
                     INNER JOIN sys.tables TAB
                     On COL.object_id = TAB.object_id
                     left join sys.types as col_type
                     on col.user_type_id = col_type.user_type_id
            END 
            --Loop를 돌며 data type 을 conversion 하면 column 개수를 1개 감소시킴 
            SET @ColumnCnt  = @ColumnCnt - 1;
 
        --Loop를 돌며 CREATE TABLE 구문을 만들면 테이블 개수를 1개 감소시킴 
        SET @TableCnt = @TableCnt - 1; 
END;
 
 
이렇게 생각해 봤는데 테이블을 무슨 값을 기준으로 하나씩 뽑을 수 있는지 모르겠습니다.
혹시 경험 있으신 분들 조언 해 주실 수 있을까요? 
테이블이 만일 50개 라고 하면 
그 50개를 어디에 저장해서 어떻게 하나씩 부를 수 있는지 모르겠습니다.
 
SET @TableName = 
    (
           SELECT tab.name
           FROM   sys.tables tab
       --WHERE 테이블을 하나씩 선택하는 조건은 무엇?
 
    )

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변