SQL insert
SQL 의 기본구문중 하나인 insert 에 대해 적어보았습니다.
INSERT~ VALUES~
INSERT 문 이후 테이블(혹은 테이블 변수나 뷰)을 지칭하고 해당 컬럼들을 정의한다.
VALUES 이후에 각 컬럼에 맞는 값목록을 지정한다.
INSERT [INTO] 테이블명(컬럼목록) VALUES(값 목록)
(INTO 는 생략 가능하다.)
이경우 반드시 컬럼 목록과 값 목록이 일치해야 한다.
컬럼은 3개인데, 값은 2개만 있다면 안된다.
그 반대로 컬럼은 A, B 이고 값은 4개라고 해도 안된다.
INSERT TABLE(A, B, C) VALUES(@A, @B)
값을 넣어줄때 컬럼에 맞게 형식, 그리고 순서도 맞추어야 한다.
테이블로 예제 보기
INSTEST 라는 테이블을 만든다.
USE TEMPDB CREATE TABLE INSTEST ( C1 INT PRIMARY KEY, C2 NUMERIC(10, 3) NOT NULL, C3 BIT NULL DEFAULT 1, C4 VARCHAR(10), C5 DATETIME )
다음의 INSERT 문을 수행하면 정상적으로 값이 넣어지게 된다.
INSERT INSTEST(C1, C2, C3, C4, C5) VALUES(1, 100.01, 1, 'HELLO', '2002-05-02 12:00.00')
또한 컬럼명을 생략할수 있지만, 이럴경우 테이블 컬럼이 위치하는 순서에 따라 값을 넣어주어야 한다.
INSERT INSTEST VALUES(1, 100.01, 1, 'HELLO', '2002-05-02 12:00.00')
문자 형식의 경우에는 파싱 때문에 ‘ (작은 따옴표)를 붙여서 처리해야 한다.
예를 들어 “HELLO”라는 문자열을 저장하려면, ‘HELLO’ 라고 적어야 한다.
작은 따옴표안에 큰 따옴표가 들어가는 것은 상관없다.
다만 작은 짝옴표 안에 다시 작은 따옴표가 들어가는 경우에는 작은 따옴표를 연속으로 2번 적어서 해결할수 있다.
“KIM’S HOUSE” 의 경우에는 ‘KIM”S HOUSE’ 라고 적는다.
날짜 형식의 경우에는 까다롭다.
일반적으로 데이터형식을 표현하기 위해서는 “2002-09-09 12:00.20” 과 같은 표현하자
이는 2002년 09월 09일 12시 00분 20초를 나타낸다.
또한 GETDATE() 메소드로 현재시각을 넣어줄수도 있다.
USE TEMPDB CREATE TABLE DATETEST(NOWDATE DATETIME NOT NULL) GO INSERT DATETEST VALUES(GETDATE()) GO
이외에도 특수한 형태의 데이터 형식이 있다.
바로 UNIQUEIDENTIFIER 데이터 형식이다.
이 데이터는 NEWID() 함수를 사용해서 값을 넣을수 있다.
USE TEMPDB CREATE TABLE UIDTest(UID UNIQUEIDENTIFIER NOT NULL) GO INSERT UIDTest VALUES(NEWID()) GO
컬럼을 생략한 INSERT
INSERT문에서 컬럼을 생략하는 경우는 대체되는 값을 SQL 서버가 자동으로 판단할수 있는 경우에만 가능하다.
대표적인 4가지 예
1. NULL 값 허용컬럼을 생략하면 NULL 할당
2. DEFAULT 값은 기본값을 할당
3. IDENTITY는 초기 값과 증가 값을 기준으로 증가된 값을 할당
4. TIMESTAMP는 데이터베이스 내에서 유일한 바이너리 값을 할당
INSERT 문에서 컬럼을 생략할 때 반드시 지켜야 할것은 컬럼을 생략하면 값도 생략해야 한다는 것이다.
흔한 잘못중 하나가 기본값이나 NULL 값이 지정된 컬럼을 그대로 두고, 값 목록에서는 해당 컬럼 값을 생략하면 기본값이나 NULL 이 지정되는 것으로 착각할수 있다.
컬럼이 지정되어 있으면 반드시 값도 지정되어야 한다. 이건 절대 규칙이다.
예제1. NULL 값의 경우
C1, C2 라는 컬럼이 두개 있는 테이블이 있다.
C1은 NOT NULL 이며, C2 는 NULL 이다.
이경우 C2는 생략할수 있다. 그럴경우 NULL 이 들어가게 된다.
CREATE TABLE NULLTest(C1 INT NOT NULL, C2 INT NULL)
INSERT NULLTest(C1) VALUES(1)
두 컬럼 모두 NULL인 경우도 생각할수 있다.
그러면 컬럼을 모두 표시하고 NULL 을 직접 할당해주어야 한다.
CREATE TABLE NULLTest2(C1 INT NULL, C2 INT NULL) INSERT NULLTest2(C1, C2) VALUES(NULL, NULL)
컬럼목록이나 값 목록이 “0”인 채로 비워져 있을수는 없다.
다음은 기본값이 지정된 컬럼이다.
NULL 값과 마찬가지로 생략하면된다.
#TABLE 은 C1 및 C2 컬럼 모두 기본값이 지정되어있다.
C2만을 생략하면 C2 컬럼은 기본값이 지정된다.
그럼 만약에 C2 컬럼이 NULL 허용이면서도 기본값이 지정되어있을 경우는 어떨까?
이런경우 기본값이 먼저다. NULL 이 아니라 기본값이 지정된다.
CREATE TABLE #TABLE(C1 INT NULL DEFAULT 1, C2 INT NULL DEFAULT 2) INSERT #TABLE(C1) VALUES(1)
기본값의 특수한점은 기본값을 지칭하는 DEFAULT 예약어를 사용할수 있다는 것이다.
특정 컬럼을 컬럼목록에 나타내고, 그 컬럼의 기본 값을 모른다면 어떨까?
값을 대체하는 DEFAULT키워드를 사용하면 문제를 해결할수 있다.
INSERT #TABLE(C1,C2) VALUES(2,DEFAULT)
DEFAULT VALUES 키워드를 사용해 전체 값 목록을 지칭할수도 있다.
이때 컬럼목록은 표시하지 말아야 한다.
INSERT #TABLE DEFAULT VALUES
만약 컬럼속성에 NULL 속성 및 DEFAULT 속성을 정해주지 않고,
값에 DEFAULT 를 넣을경우 NULL 이 들어가게 된다.
CREATE TABLE #TABLE1(C1 INT NULL DEFAULT 1, C2 INT) INSERT #TABLE2(C2) VALUES(DEFAULT)
IDENTITY컬럼은 무조건 컬럼목록에서 해당컬럼을 포함해서는 안된다.
이 값은 SQL 서버가 할당하는 것이기 때문이다.
DROP TABLE #TABLE CREATE TABLE #TABLE(C1 INT IDENTITY(1, 1), C2 INT) INSERT #TABLE(C2) VALUES(1)
그래도 IDENTITY 값을 할당하고자 하면 비기를 쓰면된다.
IDENTITY 설정을 잠시 해제하고, 값을 할당한후, 다시 IDENTITY를 설정하는것이다.
이 옵션은
SET IDENTITY_INSERT TABLE_NAME ON | OR
로 설정할수 있다.
ON을 하면 직접 INSERT 할수 있다.
SET IDENTITY_INSERT #TABLE ON INSERT TABLE2(C1, C2) VALUES(2,2) SET IDENTITY_INSERT #TABLE OFF
ROWVERSION (혹은 TIMESTAMP)는 직접 값을 할당할수 없다. @@DBTS로 해당데이터베이스의 타임스탬프 값을 받을수 있지만,
그렇다고 이 값을 넣을수 잇는것은 아니다.
그러므로 항상 이컬럼은 생략해야만 한다.
DROP TABLE #TABLE CREATE TABLE #TABLE(C1 INT, C2 ROWVERSION) INSERT #TABLE(C1) VALUES(1)
INSERT… SELECT
INSERT~ VALUES 는 오직 하나의 행(ROW, RECORD) 만을 대상으로 하는것이었다면,
INSERT… SELECT는 다중행 기반으로 INSERT 작업을 수행하는 것이라 할수있다.
이것을 로우세이트 기반 INSERT(Rowset Based) 라고 부를수있다.
이 작업의 수행방식은
우선 SELECT 문을 통해 원하는 결과를 가져온다.
가져온 결과를 INSERT에 지칭된 컬럼목록에 맞추어 해당테이블에 집어넣게 된다.
이 경우 대상되는 로우세트(ROWSET, 행집합)는 반드시 SELECT 가 아니더라도 상관없다.
로우세트를 반환하는 저장 프로시저나 뷰 등이 모두 포함된다.
CREATE TABLE TABLE1(C1 INT, C2 VARCHAR(10)) CREATE TABLE TABLE2(C1 INT, C2 VARCHAR(10)) INSERT TABLE1 VALUES(1, '100') INSERT TABLE1 VALUES(2, '200') INSERT TABLE1 VALUES(3, '300') INSERT TABLE2 SELECT C1, C2 FROM TABLE1
물론 SELECT 문 뒤에는 WHERE 조건을 써서 반환되는 레코드를 제한할수 있다.
그리고 두 테이블의 스키마가 동일할 필요가 없다.
SELECT 뒤에 컬럼리스트를 적절히 조절하거나 형식을 변환하는 것도 가능하다.
다른 스키마의 테이블을 활용하여 저장해보도록한다.
CREATE TABLE TABLE1(C1 INT, C2 VARCHAR(10)) CREATE TABLE TABLE2(C1 INT, C2 INT) INSERT TABLE1 VALUES(1, '100') INSERT TABLE1 VALUES(2, '200') INSERT TABLE1 VALUES(3, '300') INSERT TABLE2 SELECT C1, CONVERT(INT,C2) FROM TABLE1
INSERT… EXEC
저장프로시저도 동일한 일을 할수 있다.
이경우에도, 해당 저장프로시저가 로우세트를 반환해야 한다.
예를 들어 SP_WHO는 현재 데이터베이스에서 작업중인 사용자가 사용자가 실행한 명령을 보여준다.
이 결과를 테이블로 INSERT 하고자 하는경우
INSERT… EXEC… 를 사용할수있다.
CREATE TABLE TB_SP_WHO( SPID INT, ECID INT, STATUS VARCHAR(30), LOGINAME VARCHAR(30), HOSTNAME VARCHAR(30), BLK INT, DBNAME VARCHAR(30), CMD VARCHAR(30), REQUEST_ID INT ) INSERT TB_SP_WHO EXEC SP_WHO
상기의 내용은 손호성님의 “SQL Server 2005 완벽가이드 책”을 보며 필기한 내용입니다.
No Comments