SCHEMA

1. SCHEMA 생성

CREATE DATABASE schemeTestDB;
USE schemeTestDB;

CREATE SCHEMA schema_A;

CREATE SCHEMA schema_B;

CREATE TABLE schema_A.TBL_A(no INT);
CREATE TABLE schema_B.TBL_B(id INT);
CREATE TABLE schema_A.TBL_AA(code INT);

SELECT *
  FROM schema_A.TBL_A;

2. 테이블 구조 복사

: SELECT INTO을 이용해서 테이블 구조만을 복사 할 수 있다.

USE sampleDB;

SELECT *
   INTO departmentNew
  FROM department
WHERE 1 = 0;

무조건 거짓으로 설정하면 데이터를 조회할 수 없고, 테이블 구조만 복사한다.

3. SEQUENCE

CREATE SEQUENCE dept_seq
START WITH 10
INCREMENT BY 10;


INSERT departmentNew VALUES(NEXT value FOR dept_seq, '경리부', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq, '인사부', '인천');
INSERT departmentNew VALUES(NEXT value FOR dept_seq, '영업부', '대전');
INSERT departmentNew VALUES(NEXT value FOR dept_seq, '전산부', '수원');

SELECT *
  FROM departmentNew;

4. SEQUENCE의 현재 값

SELECT CURRENT_VALUE 
  FROM SYS.SEQUENCES
WHERE name = 'dept_seq';

5. SEQUENCE의 시작값을 수정하기 위한 구문

ALTER SEQUENCE dept_seq 
	RESTART WITH 15;

INSERT departmentNew VALUES(NEXT value FOR dept_seq, '생산부', '안산');
INSERT departmentNew VALUES(NEXT value FOR dept_seq, '영업부', '부산');

ALTER SEQUENCE dept_seq 
	RESTART WITH 35
	INCREMENT BY 5;

INSERT departmentNew VALUES(NEXT value FOR dept_seq, '회계부1', '광산');
INSERT departmentNew VALUES(NEXT value FOR dept_seq, '회계부2', '광산');

SELECT *
  FROM departmentNew;

6. SEQUENCE의 반복

  CREATE SEQUENCE dept_seq1
	START WITH 100
	INCREMENT BY 100
	MINVALUE 100
	MAXVALUE 500
	CYCLE;

INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부1', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부2', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부3', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부4', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부5', '서울');
INSERT departmentNew VALUES(NEXT value FOR dept_seq1, '인사부6', '서울');

SELECT *
  FROM departmentNew;

강의: ms sql 2014 제대로 배우기 2