Dreamer - I'm wendy~♥♥

MS-SQL 컬럼 길이 변경

ALTER TABLE Product
ALTER COLUMN [ProductNumber] NVARCHAR(150)

테이블 삭제

DROP TABLE Product

칼럼 변경

ALTER TABLE Product ALTER COLUMN ProductNumber NVARCHAR(100) NULL

새로운 칼럼 추가

ALTER TABLE Product ADD ProductNumber NVARCHAR(100) NULL

여러개의 새로운 칼럼 추가

ALTER TABLE Product ADD ProductNumber NVARCHAR(100) NULL,

ADD ProductName NVARCHAR(100) NULL

칼럼 삭제

ALTER TABLE member DROP COLUMN birth_day_type

[ MSDN - ALTER TABLE http://msdn.microsoft.com/ko-kr/library/ms190273.aspx ]

ALTER TABLE(Transact-SQL) : 

열과 제약 조건을 변경, 추가 또는 삭제하거나 파티션을 재할당하거나 

제약 조건과 트리거를 설정 또는 해제하여 테이블 정의를 수정합니다.

테이블 : T1

컬럼 : col1

ex1 : 인터넷 검색.

ex2 : 현재 내가 사용중인것.(부 : SQL SERVER 2005, 주 : SQL SERVER 2008 R2)

1. 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 

ex1> 

ALTER TABLE T1 ADD col1

ex2>

ALTER TABLE T1 ADD col1 NVARCHAR(20) NOT NULL

ex2> T1 테이블에 col1 칼럼이 없는 경우 col1 컬럼을 추가 한다.

DECLARE @table_id int

DECLARE @nCount int

SET @table_id = object_id('T1') 

SELECT @nCount=COUNT(name) FROM syscolumns WHERE id = @table_id and name='col1'

IF @nCount = 0

ALTER TABLE T1 ADD col1 NVARCHAR(20) NOT NULL;

2. 컬럼 수정

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 제약조건) 

ex1>

ALTER TABLE T1 MODIFY (col1 NVARCHAR(20) NOT NULL)

ex2>

ALTER TABLE T1 ALTER COLUMN col1 NVARCHAR(20) NOT NULL

3. 컬럼 삭제

ALTER TABLE 테이블명 DROP 컬럼명

ex1>

ALTER TABLE T1 DROP col1

ex2>

ALTER TABLE T1 DROP COLUMN col1

4. 컬럼 제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 

ex1> 

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (col1)

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY col1

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED  

(

[col1] ASC

)

ex2>

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED 

(

[col1] ASC,

[col2] ASC

)

5. 컬럼 제약조건 제거

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건

ex2>

DECLARE @nCount int

SELECT @nCount=COUNT(CONSTRAINT_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

WHERE TABLE_NAME = 'T1'

IF @nCount > 0

ALTER TABLE T1 DROP CONSTRAINT PK_T1

6. 컬럼명 수정

EXEC SP_RENAME 기존컬럼명, 변경할 컬럼명

ex> 

EXEC SP_RENAME 'T1', 'T2'

7. 자동 증가 컬럼에 Insert 하기

테이블에 값을 넣을때 자동 증가 컬럼에 값을 넣으려 할때면 아래와 같은 오류가 발생한다.

IDENTITY_INSERT가 OFF로 설정되면 'XXX' 테이블의 ID 열에 명시적 값을 삽입할 수 없습니다.

이럴 경우 해당 설정을 ON으로 설정 후 값을 넣은뒤 다시 OFF로 설정해 주면 된다.

*멍청한 얘기지만 당연히 INSERT 이외의 UPDATE는 이 옵션과 무관하다.(이미 옵션에_INSERT인것을...)

  자동증가 컬럼은 업데이트를 할 수 없다. 한참을 삽질을 하고 나서야 DELETE - INSERT 작업으로 처리했다.

col1 이 자동증가 컬럼인 경우.

SET IDENTITY_INSERT  테이블명 ON 또는 OFF

SET IDENTITY_INSERT  T1 ON

INSERT INTO  T1(col1) VALUES(값)

SET IDENTITY_INSERT  T1 OFF

초기화값으로 T1 테이블의 자동 증가값을 초기화 한다.

DBCC CHECKIDENT("T1",RESEED,초기화값)

8. 컬럼 기본값 설정

ALTER TABLE  T1 ADD  CONSTRAINT DF_T1_col1   DEFAULT ((0)) FOR col1

IF EXISTS (SELECT name FROM sys.objects WHERE name = N' DF_T1_col1 ' AND type=N'D' )

ALTER TABLE   T1 DROP CONSTRAINT DF_T1_col1

ELSE

ALTER TABLE   T1  ADD  CONSTRAINT   DF_T1_col1  DEFAULT ((0)) FOR col1

■  마지막으로 삽입된 ID 값을 반환하는 시스템 함수 
- INSERT 되는 테이블에 자동으로 증가하는 컬럼이 있는 경우 마지막으로 INSERT 된 자동증가 컬럼의 값을 반환합니다. 
 
 
MS-SQL
정의
@@IDENTITY
사용 방법
SELECT @@SPID
MySql
정의
LAST_INSERT_ID()
사용 방법
SET lastNumber = LAST_INSERT_ID() ;
결 과
 
 
<input class="tag_inp" title="태그를 입력해 주세요" value="" type="text" />태그저장취소
다음 예에서는 ID 열 - 자동증가 컬럼 (LocationID)이 있는 테이블에 행을 삽입하고 
@@IDENTITY 또는 LAST_INSERT_ID() 를 사용하여 새 행에 사용된 ID 값을 표시합니다.
 
 
■ 샘플 테이블 만들기 
 
MS-SQL
 
MySql
 
 
■ 한번 해 보기 
 
MS-SQL
INSERT INTO jobs (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125) 
SELECT @@IDENTITY AS 'Identity' 
MySql
INSERT INTO jobs (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125) 
SET lastNumber = LAST_INSERT_ID() ; 
SELECT lastNumber  ; 
    
 
[출처] @@IDENTITY vs LAST_INSERT_ID()|작성자 불가사리

=========================================================================================================

Multirow Considerations for DML Triggers

http://msdn.microsoft.com/en-us/library/ms190752.aspx

===================================================================================