'ms-sql'에 해당되는 글 7건
- 2020.07.30 [MSSQL] DB프로시저내 임시테이블 생성코드
- 2020.01.22 [MSSQL] 주석 추가/수정/삭제
- 2019.08.08 [MS-SQL] SQL Server Management Studio 2017 (v18.x) 다운로드
- 2019.02.07 [MSSQL] 테이블 생성/데이터 백업 - SELECT INTO vs INSERT INTO
- 2019.01.22 [MSSQL] 데이터 정렬값, COLLATION - Korean_Wansung_xxx
- 2018.08.16 [MSSQL] try..catch..rollback 예제
- 2013.04.04 [MSSQL] 주로 사용하는 SQL문 모음 [계속업데이트]
[MSSQL] DB프로시저내 임시테이블 생성코드
Posted on 2020. 7. 30. 10:17
Filed Under DB
▼ 샘플코드
--테이블변수
DECLARE @temp_REPORT_SCD_0415_PORTMATCH_HIERARCHY_COLLECT_SAVE Table
(CenterCode CHAR(6),
ModelID int,
TotalCount int,
EffectedRows int,
StartTime DateTime,
EndTime DateTime
);
--임시테이블 (주로사용)
CREATE TABLE dbo.#TmpTable (
[UID] IDENTITY(1,1),
ItemName VARCHAR(150),
InsertDate DateTime,
UpdateDate DateTime
);
▼ 상세설명
[MSSQL] 임시 테이블 vs 테이블 변수 2016.01
[MSSQL] 주석 추가/수정/삭제
Posted on 2020. 1. 22. 15:44
Filed Under DB
-- 테이블 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_updateextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명'
-- 컬럼 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';
EXEC sp_updateextendedproperty'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE','테이블명', 'COLUMN','컬럼명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';
[MS-SQL] SQL Server Management Studio 2017 (v18.x) 다운로드
Posted on 2019. 8. 8. 10:55
Filed Under 잡다구리/정보
[MSSQL] 테이블 생성/데이터 백업 - SELECT INTO vs INSERT INTO
Posted on 2019. 2. 7. 10:02
Filed Under DB
빠르게 테이블 데이터를 백업해야할 경우 CREATE TABLE 과정 없이 사용하는 쿼리
단, 인덱스, 제약 조건 등 테이블 스키마 외의 것은 복사되지 않는다.
-- 1) 테이블 생성 및 데이터 복사
SELECT * INTO 생성할_테이블명
FROM 원본_테이블명
WHERE 복사할_데이터_조건
--└ 응용: 1=2 조건으로 실행하면 테이블 스키마만 복사된 새 테이블이 생성된다.
-- 2) 새 테이블을 이미 생성한 경우 데이터만 복사
INSERT INTO 생성할_테이블명
SELECT *
FROM 원본_테이블명
WHERE 복사할_데이터_조건
[MSSQL] 데이터 정렬값, COLLATION - Korean_Wansung_xxx
Posted on 2019. 1. 22. 10:22
Filed Under DB
SELECT실행시 아래 에러 발생.
equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "Korean_Wansung_CS_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다.
원인:) 비교하는 두 컬럼의 데이터 정렬값이 달라서 발생.
해결1:) 비교컬럼에 강제캐스팅해서 비교 COLLATE
SELECT A.UsrID, A.UsrName, B.UsrGroupCd
FROM dbo.USR_USER A, dbo.USR_GROUP B
WHERE A.UsrIDCOLLATE Korean_Wansung_CI_AS
= B.userid COLLATE Korean_Wansung_CI_AS
해결2:) 데이터 정렬값을 일치 시킨다. ALTER
-- DB별 디폴트Collation조회
SELECT NAME, COLLATION_NAME, *
FROM sys.databases
-- 컬럼의 Collation 설정값 조회
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME'
-- 컬럼의 Collation설정값 변경
ALTER TABLE FORMS_USERGROUP_RANGE_TBL
ALTER COLUMN userid varchar(30)
COLLATE Korean_Wansung_CS_AS NOT NULL
Korean_Wansung_CS_AS vs Korean_Wansung_CI_AS
Korean_Wansung_CS_AS(대소문자 구분O): 양쪽 비교항에 UPPER() 사용하면 구분안함.
Korean_Wansung_CI_AS(대소문자 구분X)
[MSSQL] try..catch..rollback 예제
Posted on 2018. 8. 16. 14:17
Filed Under DB
ALTER PROCEDURE dbo.INSERT_ACTION_HISTORY
--CREATE PROCEDURE dbo.INSERT_ACTION_HISTORY
@ResultDate datetime,
@LOGID bigint,
@ActionCode int,
@Description varchar(500) = NULL
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO TBL_SAMPLE (LOGID, ActionCode, Description, UpdateDate)
VALUES (@LOGID, @ActionCode, @Description, GETDATE());
END TRY
BEGIN CATCH
-- 파라미터 정보도 저장 가능 (스트링 만들때 오류 안나도록 반드시 테스트 후에 적용할 것)
DECLARE @ParamInfo VARCHAR(1000) = '';
SET @ParamInfo =
'입력파라미터: '
+ '@ResultDate=[' + ISNULL(CONVERT(VARCHAR(19), @ResultDate, 120), 'null')
+ '], @LOGID=['+CASE WHEN @LOGID IS NULL THEN 'null' ELSE CAST(@LOGID AS VARCHAR) END
+ '], @ActionCode=['+CASE WHEN @ActionCode IS NULL THEN 'null' ELSE CAST(@ActionCode AS VARCHAR) END
+ '], @Description=['+CASE WHEN @Description IS NULL THEN 'null' ELSE SUBSTRING(@Description, 0, 100) END
+ ']';
PRINT @ParamInfo;
-- 이 부분에서 DB에러 이력 테이블에 저장하거나 출력
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
BEGIN
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
[MSSQL] 주로 사용하는 SQL문 모음 [계속업데이트]
Posted on 2013. 4. 4. 18:22
Filed Under DB
Microsoft SQL Server 2008 - Express버전은 무료. 단 DB용량이 10G이하
▼ S/W 다운로드
Microsoft SQL Server 2008 R2 RTM - Express with Management Tools
(Window Live Login 필요)
정보출처: http://devx.tistory.com/entry/최신-SQL-Server-2008-R2-Express-version
▼ Database Backup/Restore
1. backup 파일 만들기
BACKUP DATABASE DBNAME TO disk='c:\YSWL_BOOKING.bak' with init;
-- 2. DB 변경하여 restore 하기
RESTORE DATABASE DBNAME FROM DISK='c:\YSWL_BOOKING.bak'
WITH NORECOVERY,
MOVE 'DBNAME' TO 'C:\Program Files\Microsoft SQL\..\MSSQL\DATA\DBFILE.mdf',
MOVE 'DBNAME_log' TO 'C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\DBFILE_log.ldf';
출처: http://nimba.tistory.com/328
▼ 테이블 수정
1) 컬럼 추가
ALTER TABLE TableName ADD [ColumnName] [datatype] IDENTITY(1,1) NOT NULL;
→ IDENTITY(1,1)는 시퀀스 추가를 원할때 넣을 것, 1부터 1씩 증가라는 의미.
▽NOT NULL + 제약조건 추가
ALTER TABLE TBL_NAME ADD LastUpdateDate DATETIME NOT NULL CONSTRAINT DF_TBLNAME_LastUpdateDate DEFAULT GETDATE() WITH VALUES ;
2) 컬럼 수정
ALTER TABLE TableName ALTER COLUMN [ColumnName] datetime NOT NULL;
예:) ALTER TABLE EquipFileList ALTER COLUMN FileList nvarchar(250) NOT NULL;
3) 컬럼 삭제
ALTER TABLE TableName DROP COLUMN [ColumnName];
4) 컬럼명 변경
SP_RENAME 'TableName.ColumnName', 'New_ColumnName', 'COLUMN';
▼ PK/FK 추가/삭제
ALTER TABLE TableName ADD CONSTRAINT PK명 PRIMARY KEY (컬럼명);
ALTER TABLE TableName ADD CONSTRAINT FK명 FOREIGN KEY (컬럼명) REFERENCES 테이블명(컬럼명); -- FK추가
ALTER TABLE TableName DROP CONSTRAINT PK명; -- PK/FK 삭제
▼ 제약조건 추가
--디폴트값 추가
ALTER TABLE TableName ADD CONSTRAINT DF_xxx DEFAULT 기본값 FOR 컬럼명 ;
▼ 인덱스
CREATE UNIQUE INDEX UX_인덱스명 ON 테이블명 (컬럼1 ASC, 컬럼2 DESC)
WHERE 컬럼2 = 1 --with조건
DROP INDEX UX_인덱스명 ON 테이블명;
▼ MS-SQL의 데이터 정렬(인코딩) 지정 (일본어)
: SQL Server 데이터 정렬 옵션 (URL)
CI
대소 문자를 구별하지 않는다. 전각 알파벳 대소 문자도 구별하지
예 : "A"와 "a"를 동일시
CS
대소 문자를 구별한다. 전각 알파벳 대문자, 소문자도 구분
예 : "A"와 "a"를 구별하는
AI
악센트, 탁음, 대중 음악을 구별하지 않는다.
반각 카나의 탁음, 대중 음악도 구별하지
예 : "하", "바", "파"를 구분하지 않는
AS
악센트, 탁음, 대중 음악을 구별한다.
예 : "하", "바", "파"를 구별하는
KS
히라가나와 가타가나를 구분한다. 반각로 구분
KS 대해 KI는 지정하지
예 : "은"을 "이"구분
WS
반자 구분한다. 즉 전각, 반각 구별한다.
WS 대해 WI는 지정하지
예 : "a"와 "a"를 구별하는
BIN
이진 비교한다. 즉 모든 구별한다. .
지정 서식
형식 : Japanese_BIN
형식 : Japanese_ (CI | CS) _ (AI | AS) (_KS | _WS | _KS_WS)
예 : Japanese_BIN
예 : Japanese_CI_AS_KS
▼ 컬럼 데이터 정렬(COLLATE) 지정 방법(http://blog.daum.net/z-dream/17280549)
1)DB별 기본값 확인
SELECT name, collation_name FROM sys.databases;
2)컬럼별 정렬값 확인
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DB명'
AND TABLE_NAME IN ('테이블명')
3)컬럼 정렬값 변경
ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 VARCHAR(10) COLLATE 데이터정렬방식 NOT NULL;
ALTER/CREATE DATABASE DB명 COLLATE 데이터정렬방식;
▼ 관련 에러코드 1757
의미) 열 '테이블.컬럼명'의 데이터 정렬이 외래 키 ‘FK__XXX..’에 있는 참조 열 ‘테이블.컬럼명’의 데이터 정렬과 다릅니다.
▼ 데이터 정렬 정보 확인하기
SELECT name, collation_name FROM sys.databases WHERE {DB명};
▼ DateTime ↔ String 변환 [FORMAT 정보] [MSSQL 날짜 변환(GETDATE, CONVERT)]
SELECT CONVERT(Target형,원본컬럼,스트링포맷)
[DateTime to String] SELECT CONVERT(varchar,getdate(),120);
-- 120: yyyy-mm-dd hh:mm:ss(24h)
-- 112: yyyymmdd
-- 111: yyyy/mm/dd
-- 108: hh:mm:ss
[String to DateTime] SELECT CONVERT(datetime,'2013-06-28 17:28:00',120);
SELECT DATEADD(day, -1, GETDATE()), GETDATE() -- 하루전
-- 2017-12-19 10:42:50.780 2017-12-20 10:42:50.780
SELECT DATEADD(hour, -1, GETDATE()), GETDATE() -- 1시간전
--week, year, month, minute, second, millisecond
-- 초/밀리초 제거한 하루전
select GETDATE(), CAST(CONVERT(VARCHAR(16), GETDATE()-1, 120) as DateTime)
▼ 쿼리가 너무 길어서 보기 힘들때 서브쿼리 메모리에 임시보관하기
WITH VIEW1(임시뷰이름) AS (
서브쿼리문
)
SELECT * FROM VIEW1; -- 메인쿼리에서 사용
▼ DB프로시저의 시작 코드
-- 테이블에 영향을 준 행 수 메시지 출력하지 않도록 한다(∵서버 부하 적게)
-- "(1개 행이 영향을 받음)"와 같은 메시지
SET NOCOUNT ON;
-- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 읽을 수 있도록 설정한다.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
▼ 월의 첫날,마지막날짜값 조회
DECLARE @FirstDay DATETIME = CONVERT(DATETIME, '2018-02-01',121); -- 1일
DECLARE @LastDay DATETIME = DATEADD(DD,-1,DATEADD(MM,1,@FirstDay)); -- 말일
SELECT 1 AS StartDay, DATEPART(DD,@LastDay) AS EndDay;
▼ (20.11.19) 페이징 조회 OFFSET (SQL Server 2012버전 이상 지원)
DECLARE @viewCnt int = 50; -- 한 페이지 당 보여줄 ROW 갯수
DECLARE @currentPageNum int = 1; -- 조회할 페이지 번호(1~N)
--▼AS-IS
SELECT ROW_NUMBER() OVER(ORDER BY 컬럼1,컬럼2) as rnum, XXX
FROM 테이블
WHERE a.rnum BETWEEN ((@currentPageNum-1) * @viewCnt) + 1 AND (@currentPageNum * @viewCnt)
ORDER BY rnum;
--▼TO-BE
SELECT 컬럼1,컬럼2, XXX
FROM 테이블
ORDER BY 컬럼1,컬럼2
OFFSET (@currentPageNum-1)*@viewCnt ROWS FETCH NEXT @viewCnt ROW ONLY