Kamsi's Blog

foreign Key 걸려 있는 테이블 비우기 처리 하기

SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE table $table_name; 

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE [dbo].[tblcommon](
    [SEQ] [int] IDENTITY(1,1) NOT NULL,
    [GUBUN] [varchar](30) NOT NULL,
    [TITLE] [varchar](150) NOT NULL,
    [DEPTH] [int] NOT NULL,
    [PARENT_SEQ] [int] NULL,
    [SORT] [int] NOT NULL,
    CONSTRAINT [PK_tblcommon] 
    PRIMARY KEY CLUSTERED ([SEQ] ASC) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

WITH DirectReports([PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], [LVL]) AS
(
    SELECT [PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], 0 as [LVL]
    FROM tblcommon
    WHERE [PARENT_SEQ] IS NULL
    UNION ALL
    SELECT a.[PARENT_SEQ], a.[SEQ], a.[GUBUN], a.[TITLE], a.[DEPTH], a.[SORT], [LVL] + 1
    FROM tblcommon AS a INNER JOIN DirectReports AS d ON a.[PARENT_SEQ] = d.[SEQ]
)
SELECT ISNULL([PARENT_SEQ], [SEQ]) AS [PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], [LVL]
FROM DirectReports
WHERE [GUBUN] = 'DEVICE_MAKER'
ORDER BY [PARENT_SEQ], [DEPTH]