/
MSSQL DB에서 특정 데이터를 복사 해야하는 경우가 있습니다.
예를 들어
COL1,COL2,COL3 의 데이터를 가진 테이블에 PK가 COL1인 경우 에
INSERT INTO [TARGET_TBL] (COL1,COL2, COL3)
SELECT [NEW_COL1],COL2, COL3
FROM [TARGET_TBL]
WHERE COL1 = [OLD_COL1]
위와 같은 동작을 수행하는 부분입니다.
이 동작을 DB에 테이블을 조회해서 참조키 해제, 사용중인 트리거 해제 후 데이터 처리 후
이전 설정을 복원하는 구문을 공유 드립니다.
USE [TARGET DB INSTANCE]
SET NOCOUNT ON;
IF OBJECT_ID ('TEMPDB..#TARGET') IS NOT NULL
DROP TABLE #TARGET
SELECT * INTO #TARGET
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY [ID_NEW] ORDER BY [ID_OLD]) RN FROM [대상ID 저장 테이블]) A
WHERE A.RN = 1
--■ 1. 외래키 제약 조건 비활성화
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
--■ 2. OLD_ID 데이터 기준 NEW_ID INSERT ID 구문 수행
BEGIN
DECLARE @TableName NVARCHAR(500);
DECLARE @ColumnName NVARCHAR(200);
DECLARE @SelectColumns NVARCHAR(MAX);
DECLARE @SelectColumns_NEW NVARCHAR(MAX);
DECLARE @InsertColumns NVARCHAR(MAX);
DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @ID_OLD VARCHAR(100)
DECLARE @ID_NEW VARCHAR(100)
DECLARE @TRI_TBL TABLE (TRI_NAME NVARCHAR(300)) --트리거 비활성화,활성화를 위한 테이블
DECLARE @TriggerName NVARCHAR(300)
DECLARE tableCursor CURSOR FOR
SELECT DISTINCT A.NAME
FROM SYSOBJECTS A JOIN SYS.all_columns B ON A.id = B.object_id
WHERE A.TYPE = 'U' AND (B.NAME = 'ID')
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE @TRI_TBL
--사용중인 트리거 비활성화
INSERT INTO @TRI_TBL SELECT name FROM sys.triggers WHERE parent_id = OBJECT_ID(@TableName) and is_disabled = 0
DECLARE trigger_cursor CURSOR FOR SELECT TRI_NAME FROM @TRI_TBL
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DISABLE TRIGGER ' + @TriggerName + ' ON '+@TableName+';')
FETCH NEXT FROM trigger_cursor INTO @TriggerName
END
CLOSE trigger_cursor
--컬럼 처리
BEGIN
-- cursor를 사용하여 인스턴스의 모든 테이블에 대해 반복합니다.
DECLARE columnCursor CURSOR FOR
SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND (t.TABLE_NAME = @TableName);
-- cursor를 열고 반복을 시작합니다.
OPEN columnCursor;
FETCH NEXT FROM columnCursor INTO @ColumnName;
-- SELECT 및 INSERT에 사용할 컬럼 목록을 초기화합니다.
SET @SelectColumns = '';
SET @InsertColumns = '';
WHILE @@FETCH_STATUS = 0
BEGIN
-- 컬럼 목록에 현재 컬럼을 추가합니다.
SET @SelectColumns += QUOTENAME(@ColumnName) + N', ';
SET @InsertColumns += QUOTENAME(@ColumnName) + N', ';
-- 다음 컬럼으로 이동합니다.
FETCH NEXT FROM columnCursor INTO @ColumnName;
END
-- 마지막 쉼표를 제거합니다.
SET @SelectColumns = LEFT(@SelectColumns, LEN(@SelectColumns) - 1);
SET @InsertColumns = LEFT(@InsertColumns, LEN(@InsertColumns) - 1);
CLOSE columnCursor;
DEALLOCATE columnCursor;
END
-- 구문 수행
BEGIN
DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE dataCursor CURSOR FOR
SELECT CONVERT(VARCHAR(100),[ID_NEW]), CONVERT(VARCHAR(100),[ID_OLD]) FROM #TARGET
OPEN dataCursor
FETCH NEXT FROM dataCursor INTO @ID_NEW, @ID_OLD
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SelectColumns_NEW = REPLACE(@SelectColumns,'[ID]',@ID_NEW)
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
)
BEGIN
--IDENTITY_INSERT 혀용 후 SELECT 및 INSERT 쿼리를 동적으로 생성합니다.
SET @DynamicSQL = N' SET IDENTITY_INSERT '+ @TableName+' ON;
INSERT INTO ' + QUOTENAME(@TableName) + N' ( ' + @InsertColumns + N')
SELECT ' + @SelectColumns_NEW + N'
FROM' + QUOTENAME(@TableName) + N'
WHERE ID = '+@ID_OLD+';
SET IDENTITY_INSERT '+ @TableName+' OFF;'
END
ELSE
BEGIN
-- SELECT 및 INSERT 쿼리를 동적으로 생성합니다.
SET @DynamicSQL = N' INSERT INTO ' + QUOTENAME(@TableName) + N' ( ' + @InsertColumns + N')
SELECT ' + @SelectColumns_NEW + N'
FROM' + QUOTENAME(@TableName) + N'
WHERE ID = '+@ID_OLD+';'
END
-- 동적으로 생성된 쿼리를 실행합니다.
EXEC sp_executesql @DynamicSQL;
-- print @TableName+':'+ @DynamicSQL;
FETCH NEXT FROM dataCursor INTO @ID_NEW, @ID_OLD
END
CLOSE dataCursor;
DEALLOCATE dataCursor;
END
--트리거 재활성화
BEGIN
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ENABLE TRIGGER ' + @TriggerName + ' ON '+@TableName+';')
FETCH NEXT FROM trigger_cursor INTO @TriggerName
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor
END
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor;
DEALLOCATE tableCursor;
END
--■ 3. 외래키 제약 조건 활성화
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
--■ 4. 검수
MSSQL의 커서 기능과 SP_MSFOREACHTABLE 등을 활용했습니다.
[대상ID 저장 테이블] 에는 ID_OLD, ID_NEW 로 데이터가 적재되어 있음을 가정했습니다.
작업 수순은 아래와 같습니다.
1. 전체 테이블 제약조건 해제
2. 테이블 조회
2.1. 사용 중인 테이블 트리거 조회
2.1.1. 사용중인 테이블 트리거 해제
2.1.2. 테이블 컬럼 리스트 조회
2.1.2.1. 테이블 컬럼 리스트 취합
2.1.2.2. 테이블 컬럼 리스트와 데이터 컬럼 리스트 문자열 생성
2.1.3. 데이터 리스트 조회
2.1.3.1. 데이터컬럼 리스트 가공
2.1.3.2. 컬럼 중 IDENTITY 컬럼이 있는지 확인
2.1.3.2. INSERT INTO ... SELECT 구문 생성
2.1.3.3. 쿼리 수행
2.2.2. 해제된 트리거 재활성화
2.2. 테이블 순회
3. 전체 테이블 제약조건 활성화
4. 적용데이터 검수
위 내용이 모든 상황에 완벽하게 적용되기 어려울 수 있지만
여러 모로 활용은 가능 할 것이라 믿습니다.
----------------------------------------
Sometimes you need to copy certain data from MSSQL DB.
for example
If the PK is COL1 in a table with data of COL1, COL2, COL3
INSERT INTO [TARGET_TBL] (COL1,COL2, COL3)
SELECT [NEW_COL1],COL2, COL3
FROM [TARGET_TBL]
WHERE COL1 = [OLD_COL1]
This is the part that does the same thing as above.
This action is performed by querying the table in the DB, releasing the reference key, releasing the trigger in use, and processing the data.
We share the syntax to restore previous settings.
I used MSSQL's cursor function and SP_MSFOREACHTABLE, etc.
It is assumed that the [Target ID storage table] is loaded with ID_OLD and ID_NEW data.
The work flow is as follows.
-----------------------
USE [TARGET DB INSTANCE]
SET NOCOUNT ON;
IF OBJECT_ID ('TEMPDB..#TARGET') IS NOT NULL
DROP TABLE #TARGET
SELECT * INTO #TARGET
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY [ID_NEW] ORDER BY [ID_OLD]) RN FROM [TARGET ID STORED TABLE]) A
WHERE A.RN = 1
--■ 1. Disable Foreign Key Constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
--■ 2. NEW_ID INSERT ID syntax based on OLD_ID data
BEGIN
DECLARE @TableName NVARCHAR(500);
DECLARE @ColumnName NVARCHAR(200);
DECLARE @SelectColumns NVARCHAR(MAX);
DECLARE @SelectColumns_NEW NVARCHAR(MAX);
DECLARE @InsertColumns NVARCHAR(MAX);
DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @ID_OLD VARCHAR(100)
DECLARE @ID_NEW VARCHAR(100)
DECLARE @TRI_TBL TABLE (TRI_NAME NVARCHAR(300)) --Table for deactivating and activating triggers Table for deactivating and activating triggers
DECLARE @TriggerName NVARCHAR(300)
DECLARE tableCursor CURSOR FOR
SELECT DISTINCT A.NAME
FROM SYSOBJECTS A JOIN SYS.all_columns B ON A.id = B.object_id
WHERE A.TYPE = 'U' AND (B.NAME = 'ID')
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE @TRI_TBL
--Disable triggers in use
INSERT INTO @TRI_TBL SELECT name FROM sys.triggers WHERE parent_id = OBJECT_ID(@TableName) and is_disabled = 0
DECLARE trigger_cursor CURSOR FOR SELECT TRI_NAME FROM @TRI_TBL
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DISABLE TRIGGER ' + @TriggerName + ' ON '+@TableName+';')
FETCH NEXT FROM trigger_cursor INTO @TriggerName
END
CLOSE trigger_cursor
--column processing
BEGIN
-- Iterates through all tables in the instance using a cursor.
DECLARE columnCursor CURSOR FOR
SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND (t.TABLE_NAME = @TableName);
-- Open the cursor and start iterating.
OPEN columnCursor;
FETCH NEXT FROM columnCursor INTO @ColumnName;
-- Initializes the list of columns to be used for SELECT and INSERT.
SET @SelectColumns = '';
SET @InsertColumns = '';
WHILE @@FETCH_STATUS = 0
BEGIN
-- Adds the current column to the column list.
SET @SelectColumns += QUOTENAME(@ColumnName) + N', ';
SET @InsertColumns += QUOTENAME(@ColumnName) + N', ';
-- Move to the next column.
FETCH NEXT FROM columnCursor INTO @ColumnName;
END
-- Remove the final comma.
SET @SelectColumns = LEFT(@SelectColumns, LEN(@SelectColumns) - 1);
SET @InsertColumns = LEFT(@InsertColumns, LEN(@InsertColumns) - 1);
CLOSE columnCursor;
DEALLOCATE columnCursor;
END
-- phrase execution
BEGIN
DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE dataCursor CURSOR FOR
SELECT CONVERT(VARCHAR(100),[ID_NEW]), CONVERT(VARCHAR(100),[ID_OLD]) FROM #TARGET
OPEN dataCursor
FETCH NEXT FROM dataCursor INTO @ID_NEW, @ID_OLD
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SelectColumns_NEW = REPLACE(@SelectColumns,'[ID]',@ID_NEW)
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
)
BEGIN
--Dynamically generate SELECT and INSERT queries after accepting IDENTITY_INSERT.
SET @DynamicSQL = N' SET IDENTITY_INSERT '+ @TableName+' ON;
INSERT INTO ' + QUOTENAME(@TableName) + N' ( ' + @InsertColumns + N')
SELECT ' + @SelectColumns_NEW + N'
FROM' + QUOTENAME(@TableName) + N'
WHERE ID = '+@ID_OLD+';
SET IDENTITY_INSERT '+ @TableName+' OFF;'
END
ELSE
BEGIN
-- Dynamically generate SELECT and INSERT queries.
SET @DynamicSQL = N' INSERT INTO ' + QUOTENAME(@TableName) + N' ( ' + @InsertColumns + N')
SELECT ' + @SelectColumns_NEW + N'
FROM' + QUOTENAME(@TableName) + N'
WHERE ID = '+@ID_OLD+';'
END
-- Run dynamically generated queries.
EXEC sp_executesql @DynamicSQL;
-- print @TableName+':'+ @DynamicSQL;
FETCH NEXT FROM dataCursor INTO @ID_NEW, @ID_OLD
END
CLOSE dataCursor;
DEALLOCATE dataCursor;
END
--trigger reactivation
BEGIN
OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ENABLE TRIGGER ' + @TriggerName + ' ON '+@TableName+';')
FETCH NEXT FROM trigger_cursor INTO @TriggerName
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor
END
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor;
DEALLOCATE tableCursor;
END
--■ 3. Enabling Foreign Key Constraints
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
--■ 4. Inspection
-----------------------
1. Disable Entire Table Constraints
2. Table lookup
2.1. Query table triggers in use
2.1.1. Disable active table triggers
2.1.2. Query table column list
2.1.2.1. Collect table column list
2.1.2.2. Create table column list and data column list string
2.1.3. Data list search
2.1.3.1. Data column list processing
2.1.3.2. Check if there is an IDENTITY column among the columns
2.1.3.2. Generate an INSERT INTO ... SELECT statement
2.1.3.3. query
2.2.2. Reactivating a released trigger
2.2. table traversal
3. Enable Full Table Constraints
4. Applied data inspection
Although the above information may not be perfectly applicable in all situations,
I believe that it can be used in many ways.
'프로그래밍 > TSQL' 카테고리의 다른 글
| [MSSQL]EXECUTE 후 트랜잭션 개수는 BEGIN 및 COMMIT 문 수가 일치하지 않음을 나타냅니다 (0) | 2023.07.20 |
|---|---|
| [TSQL]mssql cursor 재사용 방법 (0) | 2023.06.20 |
| [MSSQL] TEMPDB 용량 축소, 관리 구문 (0) | 2023.06.08 |
| [TSQL]"엄청 빠른 ROW COUNT" COUNT(*) 는 너무 느려 SYS.DM_DB_PARTITION_STATS 을 써보자 (0) | 2023.04.11 |
| [MSSQL] bcp 중에 QUOTED_IDENTIFIER 관련 오류 발생 해결 방법 (0) | 2022.08.11 |
댓글