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 테이블에 데이터가 제대로 들어가는 것을 확인 할 수 있다.

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

2014/08/05 - [서버에웹환경구축하기/5.마리아DB(MariaDB) 설치] - [서버구축 - 11] 서버에 MySQL과 동일한 마리아DB(MariaDB) 설치하기

2014/08/05 - [서버에웹환경구축하기/5.마리아DB(MariaDB) 설치] - [서버구축 - 12] 마리아DB(MariaDB) 인코딩 UTF-8 설정하기

mysql 설치 시 UTF-8 환경으로 default 설치 되는 경우도 있지만, 본인처럼 한글이 깨지는 현상 발생시 mysql 환경을 utf-8으로 맞춰주는 포스팅을 진행하고자 한다.

대부분 mysql 기본 설치 시, 인코딩 타입이 latin(?) 타입으로 주로 설치되는것을 확인 할 수 있었다.

다음 명령어를 통하여 본인의 mysql 문자 인코딩을 확인하도록 하자

SHOW VARIABLES LIKE 'c%'

확인결과 system 부분만 utf8이고 그외에는 latin1으로 인코딩 되어있다.

다음 파일을 수정하여 utf8 환경으로 변경 하도록 하자

윈도우일경우 my.ini 

리눅스일경우 my.cnf

파일을 찾아서 각각 위치에 맞추어 추가를 해주도록 하자

본인은 local환경이 윈도우에 APM 파일을 설치하여 확인한 결과이다.

100% 되는방법이라고 장담은 못한다. 보편적인 방법을 설명하는 것 뿐이다.

본인의 경우, 윈도우/리눅스 MySQL 설치 후,  인코딩 설정시에는 적용이 되서 포스팅을 한것임.

[client]
.
.
#하단추가
default-character-set = utf8

[mysqld]
.
.
#하단추가
init_connect="SET collation_connection = utf8_general_ci"  
init_connect="SET NAMES utf8"  
default-character-set = utf8  
character-set-server = utf8  
collation-server = utf8_general_ci  

[mysqldump]
.
.
#하단추가
default-character-set = utf8

[mysql]
.
.
#하단추가
default-character-set = utf8

데몬 재실행 후 다시한번 인코딩 확인 명령어를 찍어서 확인해보도록 하자

위와같이 대부분 utf8로 잡혔다 하지만 database 부분에 대해서는 latin1이 여전히 적용되있었다 .

아마 기존에 신규 생성된 database가 latin1으로 생성되서 그런거 같다.

그래서 신규 생성했던 2가지 database 를 제거 후 다시한번 확인 해보았다. 

확인결과 정상적으로 UTF-8 로 인코딩 타입이 적용되었다.

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

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에 대한 부분은 고민하고 수정해보도록 하자.

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

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