2014/08/23 - [DB/MySQL] - MySQL - 널체크(IFNULL 사용하기)

2014/08/23 - [DB/Oracle] - 오라클 - 널체크(NVL 사용하기)

지난에 포스팅했던 MySQL 널체크에 이어  이번에는 MS-SQL 널체크(ISNULL)에 대하여 포스팅을 해보고자 한다.

create table null_table (
  idx bigint IDENTITY(1,1) NOT NULL  PRIMARY KEY,
  null_text varchar(20)  NULL 
)

insert into null_table(null_text)
values('1');
insert into null_table(null_text)
values(null);
insert into null_table(null_text)
values('2');
insert into null_table(null_text)
values(null);
insert into null_table(null_text)
values('3');

SELECT * 
FROM null_table

select *, ISNULL(null_text,'널입니다') null_check
from null_table

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/15 - [DB/MySQL] - MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자

2014/08/15 - [DB/Oracle] - ORACLE 오라클 rownum을 이용하여 페이징 처리하기

이번에는 MS-SQL 2005버전 이상부터 지원을 하는 ROW_NUMBER() OVER(ORDER BY 정렬조건) 을 이용하여 페이징 처리를 다루도록 해보겠음.

MS-SQL 2000 버전에는 TOP을 이용하여 페이징 처리를 하였는데 이부분은 페이지가 뒤로 가면 갈수록 

느려지는 단점이 있다.

2005부터 ROW_NUMBER를 제공하기 시작했는데 ORACLE만큼 페이징이 편해졌다.

역시 샘플 데이터를 기준으로 설명하도록 하겠음.

create table page_table (  
  [idx] bigint  NULL ,  
  [title] varchar(100)  NULL ,  
  [conetnt] varchar(200)  NULL 
);  

page_table_data.sql

구조 및 가상의 데이터는 이전에 포스팅한 mysql 페이징과 같다

그렇다면 이제 페이징 테스트를 해보도록 하겠음.

각 페이징 처리를 할때마다 20개씩 출력해보도록 하겠다.

쿼리는 다음과 같다.

-- 1페이지
SELECT *
FROM (
  SELECT row_number() over(order by idx) rownum,*
    FROM page_table
) pagetable
where rownum between 1 and 20
-- 2페이지
SELECT *
FROM (
  SELECT row_number() over(order by idx) rownum,*
    FROM page_table
) pagetable
where rownum between 21 and 40
.
.
-- 5페이지
SELECT *
FROM (
  SELECT row_number() over(order by idx) rownum,*
    FROM page_table
) pagetable
where rownum between 81 and 100
-- 존재하지 않는 6페이지
SELECT *
FROM (
  SELECT row_number() over(order by idx) rownum,*
    FROM page_table
) pagetable
where rownum between 101 and 120

쉽게 생각해서 테이블 데이터아 rownum이라는 컬럼을 추가해서 1부터 출력된 테이블의 갯수를 

순차적으로 증가 시켜주는거라고 생각하면 될것이다.

WHERE 절에 

where rownum컬럼 between start and end

라는 조건을 주어서 시작ROWNUM ~ 마지막으로 출력하고자하는 ROWNUM

을 데이터에 출력을 하는것이다.

물론 BETWEEN으로 안하고 

>=  AND <=  

이와같은 문법을 이용하여 페이징 처리를 해주어도 무관함.

마지막 존재하지 않는 6페이지일경우는 그냥 한번 출력해보았음.

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/12 - [DB/MySQL] - MySQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...)

2014/08/13 - [DB/Oracle] - 오라클(Oracle) 데이터를 일괄로 다른테이블에 INSERT 하기 (INSERT INTO ... SELECT ... FROM ...)

이번 포스팅은 MS-SQL에서 INSERT SELECT문과 SELECT INTO FROM문을 이용하여 데이터를 

일괄로 삽입 해보고자 한다.

CREATE TABLE temp_table(
   id varchar(20),
  title varchar(20),
  flag varchar(1)
)

CREATE TABLE test_table (
   id varchar(20),
  title varchar(20)
)

insert into temp_table(id,title,flag) values('1','제목1','Y');
insert into temp_table(id,title,flag) values('2','제목2','N');
insert into temp_table(id,title,flag) values('3','제목3','Y');
insert into temp_table(id,title,flag) values('4','제목4','Y');
insert into temp_table(id,title,flag) values('5','제목5','N');
insert into temp_table(id,title,flag) values('6','제목6','Y');
insert into temp_table(id,title,flag) values('7','제목7','Y');
insert into temp_table(id,title,flag) values('8','제목8','N');
insert into temp_table(id,title,flag) values('9','제목9','Y'); 

이제부터 temp_table -> test_table에 id값과 title값만을 insert하고자 한다.

조건으로 flag 값이 'Y' 인 데이터만을 INSERT 하도록 하겠음

INSERT test_table(id,title)
SELECT id,title
FROM temp_table
WHERE flag = 'Y'

정상적으로 test_table에 flag값이 Y인 데이터가 일괄로 들어같 것을 확인 할 수 있었다.

※ 기존 테이블과 신규 테이블구조가 동일하면 하단처럼 INSERT SELECT 가능

INSERT test_table
SELECT * FROM temp_table

이번 방법은 TABLE COPY 및 CREATE VIEW와 비슷한 개념인듯..

사용법

SELECT [컬럼 1] , [컬럼 2]
INTO [신규 생성할 테이블명]
FROM [기존 테이블명]

위와 같은 구조이다.

예를 들어 다음쿼리를 실행시켜 보도록 하자 

SELECT * 
     INTO into_table 
   FROM temp_table

temp_table의 데이터를 into_table을 생성하여 모든 데이터를 insert 하겠다 라는 것과 같은 내용이다.

확인결과 기존 temp_table의 테이블구조 + 데이터가 동일하게 into_table로 생성 및 insert 되었다.

※ INSERT ... SELECT VS SELECT .. INTO .. FROM 차이점

    - INSERT SELECT : INSERT할 테이블 및 SELECT 할 테이블이 모두 존재

    - SELECT INTO : SELECT 테이블이 존재하고 INSERT 테이블이 존재하지 않을경우

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/12 - [DB/Oracle] - 오라클 계층형 트리구조 쿼리 (재귀호출) - START WITH .. CONNECT BY PRIOR

MS-SQL 2005 부터 추가된 재귀쿼리를 설명 하고자 한다.

트리구조 쿼리에 사용하면 유용할 거 같아서 포스팅을 하게 되었다.

with xxx(col 1,col 2...col n)

as ( 

 table 

 union

 table

)

select * from xxx order by order column

오라클 재귀쿼리 호출했던 같은 구조의 테이블로 샘플을 짜보겠다.

2014/08/12 - [DB/Oracle] - 오라클 계층형 트리구조 쿼리 (재귀호출) - START WITH .. CONNECT BY PRIOR

create table tree_table(
	id int not null,
    parent_id int not null,
    name nvarchar(30) not null
);

-- 1depth 
insert into tree_table(id,parent_id,name)
values(1,0,'1depth 1');
insert into tree_table(id,parent_id,name)
values(2,0,'1depth 2');
insert into tree_table(id,parent_id,name)
values(3,0,'1depth 3');

-- 2depth 1
insert into tree_table(id,parent_id,name)
values(4,1,'2depth 1-1');
insert into tree_table(id,parent_id,name)
values(5,1,'2depth 1-2');
insert into tree_table(id,parent_id,name)
values(6,1,'2depth 1-3');

-- 2depth 2
insert into tree_table(id,parent_id,name)
values(7,2,'2depth 2-1');
insert into tree_table(id,parent_id,name)
values(8,2,'2depth 2-2');
insert into tree_table(id,parent_id,name)
values(9,2,'2depth 2-3');

-- 3depth 1
insert into tree_table(id,parent_id,name)
values(10,4,'3depth 1-1');
insert into tree_table(id,parent_id,name)
values(11,4,'3depth 1-2');
insert into tree_table(id,parent_id,name)
values(12,4,'3depth 1-3');

WITH tree_query  AS (
  SELECT  
         id
       , parent_id
       , name
       , convert(varchar(255), id) sort  
  	   , convert(varchar(255), name) depth_fullname
    FROM tree_table
    WHERE parent_id = 0
    UNION ALL 
    SELECT
          B.id
        , B.parent_id
        , B.name
        , convert(varchar(255), convert(nvarchar,C.sort) + ' > ' +  convert(varchar(255), B.id)) sort
        , convert(varchar(255), convert(nvarchar,C.depth_fullname) + ' > ' +  convert(varchar(255), B.name)) depth_fullname
    FROM  tree_table B, tree_query C
    WHERE B.parent_id = C.id
) 
SELECT id, parent_id, name, depth_fullname FROM tree_query order by SORT

위의 재귀쿼리 실행결과를 확인해 보도록 하자 

위와같이 트리구조로 출력되는것을 확인하였다. 

구조로 따지자면,

   1depth 1

   |

   -------- 2depth 1-1

   |                 |

   |                 ---- 3depth 1-1

   |                 |

   |                 ---- 3depth 1-2

   |                 |

   |                 ---- 3depth 1-3

   -------- 2depth 1-2

   |

   -------- 2depth 1-3

   |

   1depth 2

   |

   -------- 2depth 2-1

   |

   -------- 2depth 2-2

   |

   -------- 2depth 2-2

   |   

   1depth 3

와같은 구조로 출력되었다.

정렬이 중요한대 위의 정렬은 데이터가 많지 않아 잘 나오는거같다.

만약 재귀쿼리가 정상적으로 출력이 되지 않으면 sort에 대한 부분은 고민하고 수정해보도록 하자.

마지막으로 잘 활용해서 실무에 적용해보도록 합시다~ 

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/11 - [DB/Oracle] - 오라클(Oracle) sequence 생성, 등록, 최종 시퀀스값 조회하기

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

CREATE TABLE test_board(
  seq INT NOT NULL identity(1,1) PRIMARY KEY,
  title VARCHAR(20) NOT NULL
);

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');

위와같이 실행 후 SELECT문으로 데이터 조회

해당 테이블 구조 및 데이터는 

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

과 동일함

그럼 이제 모든 데이터를 지우고 다시 같은 insert 쿼리를 동작 후 SELECT문으로 조회를 해보도록 

하자 

이처럼 seq 컬럼에 데이터가 지워진 시퀀스 데이터에 이어져서 자동증가하는 것을 확인하였다.

시퀀스값을 깔끔하게 1부터 시작하기 원하는 경우가 존재한다.

다음을 이용하여 MS-SQL 시퀀스를 초기화 해보도록 하자

시퀀스 초기화를 하고자 한다면 다음과 같은 명령어를 주도록 하자

DBCC CHECKIDENT('test_board', RESEED, 0)

위와같은 DBCC CHECKIDENT 함수(?) 실행후 'test_board' 에 초기화 하고자 하는 테이블을 정의 한 후 세번째 파라미터 값에 초기화 값을 지정해주도록 하자

정상적으로 초기화가 되는지 시나리오 작성을 해보도록 하겠음

1) 테이블의 data all delete

2) 시퀀스 초기화(DBCC CHECKIDENT('xxxxxx','RESEED',x))

3) insert문을 디용한 데이터 추가

4) select문을 통한 table data 확인

-- 1. 데이터 모두 삭제
DELETE FROM TEST_BOARD;

-- 2. 시퀀스 초기화
DBCC CHECKIDENT('test_board', RESEED, 0)

-- 3. INSERT문 실행

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');


-- 4. SELECT문 조회

SELECT * FROM test_board

위와같이 정상적으로 seq 컬럼의 시퀀스가 초기화 되는것을 확인 할 수 있었다.

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!