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/15 - [DB/MSSQL] - MS-SQL 2005 이상 row_number() over(order by 정렬조건) 을 이용하여 페이징쿼리를 동작시켜보자

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

mysql의 경우는 limit를이용하여 페이징을 처리 한다.

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

CREATE TABLE `page_table` (
  `idx` bigint(20) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

page_table_data.sql

가상의 데이터를 100개를 생성하여 page_table에 IMPORT 하였다.

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

각 페이지마다 데이터 20개씩 출력해보도록 하겠다.

쿼리는 다음과 같다.

-- 1페이지
SELECT * 
FROM page_table
LIMIT 0, 20;
-- 2페이지
SELECT * 
FROM page_table
LIMIT 20, 20;
-- 3페이지
SELECT * 
FROM page_table
LIMIT 40, 20;
-- 4페이지
SELECT * 
FROM page_table
LIMIT 60, 20;
-- 5페이지
SELECT * 
FROM page_table
LIMIT 80, 20;
-- 6페이지
SELECT * 
FROM page_table
LIMIT 100, 20;

SELECT [col1,col2...col n]
FROM table명
WHERE[AND] 조건절....
LIMIT 인덱스 값(0부터 시작) , 한번에 출력할 수 

※ 상단 SELECT쿼리를 실행한 화면을  보면 마지막 6페이지는 나오지 않을것이다.

    이유 - 전체 데이터의 마지막 인덱스 값이 99인대 100부터 20개를 출력하겠다라고 하였으니 

             나오지 않는것 

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

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

2014/08/13 - [DB/MSSQL] - MS-SQL SELECT 쿼리로 다른 테이블로 INSERT (INSERT SELECT FROM , SELECT INTO FROM)

오라클에서 데이터 이관하는 작업과 같은 일괄로 다른 테이블에 INSERT해야하는 경우가 생긴다.

이럴경우 INSERT INTO .. SELECT ... FROM 을 이용하여 다른 테이블에 데이터를 

일괄 등록이 가능하다

간단하게 진행을 해보도록 하자

create table original_table (
  id varchar(10),
  title varchar(20),
  flag varchar(1)
)

create table new_table (
  id varchar(10),
  title varchar(20)
)

insert into original_table (id,title,flag) values('1','제목11','Y');
insert into original_table (id,title,flag) values('2','제목22','Y');
insert into original_table (id,title,flag) values('3','제목33','Y');
insert into original_table (id,title,flag) values('4','제목44','Y');
insert into original_table (id,title,flag) values('5','제목55','Y');
insert into original_table (id,title,flag) values('6','제목66','Y');
insert into original_table (id,title,flag) values('7','제목77','Y');
insert into original_table (id,title,flag) values('8','제목88','N');
insert into original_table (id,title,flag) values('9','제목99','Y'); 

INSERT INTO new_table(id,title)
SELECT id,title
  FROM original_table 
WHERE flag = 'N'

SELECT * FROM new_table

역시 예상대로 ID값이 8인 데이터만 INSERT 되었다.

일괄로 모든 데이터 넣고 싶을경우에는 WHERE 조건만 빼면 될것이다.

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

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/13 - [DB/Oracle] - 오라클(Oracle) 데이터를 일괄로 다른테이블에 INSERT 하기 (INSERT INTO ... SELECT ... FROM ...)

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

이번에는 기존에 데이터가 쌓여있는 테이블의 존재하는 모든 데이터를 일괄로 신규로 생성한 테이블에 옮기고자 할때  INSERT ... SELECT ... FROM 을 이용하여 데이터를 이관할 수 있다.

우선 2가지 테이블이 존재한다고 가정하자  

create table temp_table(
  id varchar(10),
  title varchar(20),
  flag varchar(1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

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'); 

create table real_table(
  id varchar(10),
  title varchar(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

※ 위와같이 2개의 테이블 생성한 후 temp_table에만 데이터를 넣어놓은 상태이다

    temp_table -> real_table에 id,title 컬럼만 일괄로 넣고자 한다.

    하지만 조건으로 flag값이 'N'인 데이터만 넣고자 한다.

    그렇다면 결론적으로 real_table에 들어가야 하는 데이터들은

    id값이 2,5,8 인 데이터들만 들어가야 할 것이다 .

INSERT real_table(id,title)
SELECT id,title
FROM temp_table
WHERE flag = 'N'

위와 같이 정상적으로 temp_table의 데이터 중 flag값이 'N' 인 3개의 데이터들이 id,title값만 real_table 테이블에 데이터가 제대로 들어가는 것을 확인 할 수 있다.

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