본문 바로가기

데이터베이스/MSSQL42

MSSQL - 프로시저에서 SELECT 절의 값을 변수에 할당하는 방법 MSSQL 프로시저에서 변수를 선언하고 SELECT절의 값을 변수에 할당하는 방법입니다. 변수 선언 DECLARE @변수명 데이터타입; DECLARE @INT_VAL INT; DECLARE @STR_VAL VARCHAR(200); 변수에 값 할당 변수에 값을 할당 할때는 앞에 SET을 기술하고 할당하면 됩니다. SET @INT_VAL = 10; SET @STR_VAL = 'ABCDED'; SELECT 절의 여러 값을 변수에 할당 하는 방법 SELECT 절의 여러 컬럼의 값을 할당 할 수도 있습니다. ROW가 여러개인 경우 마지막 값이 할당되는점 주의하세요. DECLARE @INT_VAL INT; DECLARE @STR_VAL VARCHAR(200); WITH TEST AS ( SELECT 10 VAL1,.. 2023. 7. 12.
MSSQL 문자 함수 모음 MSSQL에서 사용 할 수 있는 문자 관련 함수입니다. ASCII : 왼쪽 첫번째 문자의 ASCII코드 값을 반환 -- ASCII(character_expression) SELECT ASCII('ABC') CHAR : ASCII 코드를 문자로 변환 -- CHAR (integer_expression) SELECTchar(65) CHARINDEX : expression2에서 expression1의 시작 INDEX 반환, 없으면 0 반환 -- CHARINDEX( expression1,expression2 [ , start_location] ) SELECT CHARINDEX('다', '가나다라마', 2) LEFT, RIGHT : 문자열의 왼쪽 또는 오른쪽부터 지정된 수만큼의 문자를 반환 -- LEFT,RIGHT.. 2023. 6. 28.
MSSQL 여러 Row를 하나로 합치기 여러 ROW의 컬럼의 값을 하나로 합치는 방법입니다. XML 만드는 쿼리를 이용하는데, 아래와 같이 쿼리를 실행하면 NAME 태그로 XML이 표시됩니다. WITH A AS ( SELECT '이순신' NAME UNION ALL SELECT '홍길동' NAME UNION ALL SELECT '김유신' NAME ) SELECT NAME FROM A ORDER BY NAME FOR XML PATH('') XML 태그를 없애기 위해서는 컬럼명을 지정하시 않게 해야 하는데, 구분자(,)를 넣고 ALIAS(별칭)를 지정하지 않습니다. WITH A AS ( SELECT '이순신' NAME UNION ALL SELECT '홍길동' NAME UNION ALL SELECT '김유신' NAME ) SELECT ',' + NA.. 2023. 6. 28.
MSSQL WITH NOLOCK 설정하기 MSSQL에서 트랜잭션이 진행중인 테이블을 SELECT 하면 트랜잭션이 끝날때까지 SELECT는 팬딩됩니다. 트랜잭션이 끝날때까지 기다리지 않고 SELECT 하기 위해서는 FROM절의 테이블마다 WITH (NOLOCk)를 넣어 주어야 합니다. SELECT * FROM [테이블명] WITH(NOLOCK) 매번 테이블에 WITH(NOLOCK)을 넣기에는 너무 귀찮죠..^^ SELECT 쿼리 수행 전에 아래의 와 같이 NOLOCK을 설정하면 FORM 절에 WITH(NOLOCK) 을 넣지 않아도 됩니다. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 2023. 6. 22.
MSSQL 여러 컬럼의 MIN, MAX, SUM, AVG 구하기 하나의 Row에 여러 컬럼 중 최소값, 최대값, 합계 등을 구하는 방법입니다. 많이 사용하지 않는 쿼리지만 알고 있으면 힘이 되겠죠. 아래의 간단한 예제를 보고 쉽게 따라 할 수 있을 겁니다. WITH T AS ( SELECT 12 A, 13 B, 56 C, 123 D, 3 E ) SELECT (SELECT MIN(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) MIN_VAL, -- 최소값 (SELECT MAX(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) MAX_VAL, -- 최대값 (SELECT SUM(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) SUM.. 2023. 6. 13.
MSSQL 문자 숫자 변환 및 숫자 체크 MSSQL에서 문자를 숫자로 변환해야 하는 경우가 종종 있습니다. 그런데 문자에 숫자가 아니 다른 문자가 있을 경우 오류가 발생하게 되는데 이런 경우 처리 방법을 알아보겠습니다. 문자를 숫자로 변화 쿼리 SELECT CONVERT(INT, '1234'), CONVERT(FLOAT, '1234.123'), CONVERT(NUMERIC, '1234.12345') 문자에 숫자가 아닌 경우 쿼리에 오류 발생 SELECT CONVERT(INT, 'a1234') ERROR : varchar 값 'a1234'을(를) 데이터 형식 int(으)로 변환하지 못했습니다. 문자를 숫자로 변화 가능 여부 쿼리 SELECT ISNUMERIC('a1234'), -- 0 (숫자 변환 X) ISNUMERIC('1234') -- 1 (.. 2022. 8. 18.
MSSQL에서 오라클 DECODE 처럼 쿼리 사용하기 오라클의 DECODE는 매우 유용한 함수입니다. MSSQL에서는 DECODE가 없어 처음에는 불편했습니다. 오라클의 DECODE처럼 사용 할 수 있는 방법을 소개합니다. 아래의 쿼리 예제를 실행해보면 쉽게 이해 할 수 있을 겁니다.^^ IIF 하나의 조건에 따라 참과 거짓으로 값을 표시 할 수 있는 함수입니다. IIF( boolean_expression, true_value, false_value ) 쿼리 예제 WITH CITY AS ( SELECT 10 CODE, '서울' NAME UNION SELECT 20 CODE, '부산' NAME UNION SELECT 30 CODE, '대구' NAME UNION SELECT 40 CODE, '대전' NAME ) SELECT NAME, IIF(CODE = 10,.. 2022. 7. 14.
MSSQL 분모가 0 일때 처리 방법 MSSQL 데이터베이스의 데이터로 계산하는 쿼리에서 분모가 0일 때 아래와 같은 오류가 발생합니다. WITH TEST AS ( SELECT 10 A, 0 B ) SELECT A / B FROM TEST 0으로 나누기 오류가 발생했습니다. 아래의 쿼리 같이 IIF 또는 CASE를 사용하여 0 인 경우를 처리하기도 합니다. WITH TEST AS ( SELECT 10 A, 0 B ) SELECT IIF(B = 0, 0, A / B), CASE WHEN B = 0 THEN NULL ELSE A / B END FROM TEST 다른 방법으로 ANSI_WARNINGS, ARITHIGNORE, ARITHABORT을 설정하는 방법입니다. ANSI_WARNINGS ON으로 설정한 경우 SUM, AVG, MAX, MIN.. 2022. 7. 7.
MSSQL 개월 차이에 대해서 MSSQL에서 두 날짜의 개월 차이를 구하는 함수로 DATEDIFF를 사용하면 됩니다. 하지만, DATEDIFF를 사용하면 일자까지를 계산하는 것이 아닌 단순히 월의 차이만을 구합니다. 즉, 올림을 하는 현상이 발생합니다. 아래의 쿼리는 8.x로 계산이 되어야 하지만, 월만으로 계산하여 9개월 나옵니다. SELECT DATEDIFF(MONTH, CONVERT(DATE, '20150529'), CONVERT(DATE, '20160201')) 개월수를 버림으로 처리하고 싶다면 아래의 쿼리로 처리해야 합니다. 결과는 8개월이 됩니다. SELECT CASE WHEN DATEPART(DAY, CONVERT(DATE, '20150529')) > DATEPART(DAY, CONVERT(DATE, '20160201').. 2022. 5. 24.
MSSQL CASE WHEN 조건절에서 NULL 처리 쿼리에서 CASE WHEN 조건절을 사용 시 간혹 NULL을 조건으로 처리해야 하는 경우가 있습니다. ISNULL을 이용하여 사용하기도 했는데 더 정확하게 할 수 있는 방법있네요. 아래의 CASE WHEN 조건절을 참고하세요. CASE WHEN 필드명 IS NULL THEN 값 WHEN NOT 필드명 IS NULL THEN 값 ... END 2021. 10. 21.
MSSQL 행 번호 매기기 MSSQL에서 행(Row) 번호 매기는 방법입니다. 문법 SELECT ROW_NUMBER() OVER(PARTITION BY columnName ORDER BY columnName) AS NO FROM tablename 행번호 쿼리 WITH EX AS ( SELECT '한국' NAME, '아시아' REGION UNION SELECT '미국', '북아메리카' UNION SELECT '캐나다', '북아메리카' UNION SELECT '영국', '유럽' UNION SELECT '독일', '유럽' UNION SELECT '중국', '아시아' UNION SELECT '필리핀', '아시아' ) SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS NO, NAME, REGION FROM EX .. 2021. 8. 4.
MSSQL FORMAT 문자를 활용한 DATETIME 표시하기 MSSQL에서 날짜 타입을 Format으로 변경 CONVERT를 사용하기도 하는데 숫자로 되어 있어 매우 불편합니다. MSSQL에서도 오라클에서 처럼 FORMAT 문자를 이용하여 변환 할 수 있습니다. FORMAT 문자 yyyy - 년도 4자리(2021) yy - 년도 2자리(21) MM - 월(01~12) dd - 일(01-31) hh - 시간 12시로 표시(01~12) HH - 시간 24시로 표시(00~23) mm - 분(00~59) ss - 초(00~59) tt - 오전(AM)/오후(PM) 쿼리 예제 쿼리 결과 SELECT FORMAT (getdate(), 'yyyy-MM-dd') 2021-07-29 SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss') 2021-0.. 2021. 7. 29.
MSSQL에서 오라클 MINUS 사용하기 오라클의 MINUS와 같은 기능으로 MSSQL에서는 EXCEPT를 사용하면됩니다. EXCEPT는 두개의 쿼리에서 동일한 값을 빼는 기능입니다 문법 { | ( ) } { EXCEPT } { | ( ) } 아래는 예로 전체 테이블에서 이름(NAME)이 '이%' 로 시작하는 데이터를 삭제하는 쿼리입니다. SELECT ID, NAME FROM TABLE_NAME EXCEPT SELECT ID, NAME FROM TABLE_NAME WHERE NAME LIKE '이%' 2021. 7. 19.
MSSQL 날짜, 주차 데이터 없을 경우 MASTER..SPT_VALUES을 이용한 데이터 만들기 MASTER..SPT_VALUES의 데이블 데이터 템플릿으로 생각됩니다. 일정 기간의 날짜 또는 주차 등의 데이터를 만들경우 유용하게 사용 할 수 있습니다. MASTER..SPT_VALUES의 TYPE이 'P'인 데이터를 이용하여 날짜, 주차를 구하는 쿼리입니다. SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' 1. 시작일 종료일로 날짜 데이터 쿼리 SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '2021-07-01'), 23) FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER 2021. 7. 15.
MS SQL 버전 확인 MS SQL에서 Select 결과를 JSON으로 변경 할 수 있는 기능 FOR JSON을 사용하려니 오류가 발생했습니다. FOR JSON을 사용하기 위해서 MS SQL의 버전이 2016(13.x) 이상이어야 하네요. MS SQL의 버전을 확인하는 쿼리는 아래와 같습니다. select @@version [결과] 2021. 3. 3.
MSSQL 스키마(소유자) 변경하기. MSSQL에서 스키마(소유자)를 변경하는 방법입니다.아래의 Alter문은 하나의 테이블의 스키마를 변경하는 방법입니다. ALTER SCHEMA [변경할 스키마명] TRANSFER [기존 스키마명].[변경할 테이블명] ex) ALTER SCHEMA SchemaName TRANSFER dbo.TableName 2018. 9. 19.
MSSQL에서 Openquery를 사용하지 않고 Linked Server에 쿼리 수행하기 Linked Server에 쿼리를 수행하기 위해 Openquery를 사용합니다. 하지만 Openquery를 사용하면 쿼리를 문자열 인자로 넣아야 하기 때문에 가독성이 매우 좋지 않습니다. 따라서 하기의 내용은 Openquery를 사용하지 않고 Linked Server에 쿼리를 수행하는 방법입니다. (1) MS-SQL Linked Server - Syntax : SELECT * FROM [Liked Server Name].[Database Name].dbo.[Table Name] SELECT * FROM ERP.ERP_MDB.dbo.PartMaster (2) Oracle Linked Server - Syntax : SELECT * FROM [Liked Server Name]..[Catalog Name].[.. 2017. 12. 28.
MS-SQL 반올림, 올림, 내림. MS-SQL에서의 숫자 반올림, 올림, 내림입니다. 아래의 쿼리를 참고하시면 쉽게 이해하실 겁니다. -- 반올림 SELECT ROUND(555.555, 2) -- 소숫점 세째자리 반올림 SELECT ROUND(555.555, 1) -- 소숫점 둘째자리 반올림 SELECT ROUND(555.555, 0) -- 소숫점 첫째자리 반올림 SELECT ROUND(555.555, -1) -- 일의 자리 반올림 SELECT ROUND(555.555, -2) -- 십의 자리 반올림 -- 올림 SELECT CEILING(555.555) SELECT CEILING(555.444) -- 내림 SELECT FLOOR(555.555) SELECT FLOOR(555.444) 2017. 11. 14.
MSSQL MERGE 쿼리 활용하기. MSSQL 데이터베이스에 특정 Rows를 Update 하거나 Insert를 쉽게 하는 방법으로 MERGE 사용합니다. 아래의 TB_USER 테이블에 ID가 'aaa'이고, Name이 'Name#1' 데이터를 넣고자 하는데, 동일 아이디가 있으면 UPDATE하고, 없으면 INSERT를 하려고 한다. 프로그램에서 구현하려면 INSERT 쿼리를 수행해서 오류(PK 중복 Exception)가 발생하면 UPDATE를 해야 한다. C# 또는 Java로 프로그래밍하면 try catch를 이용 할 수 있다. try { // Insert 쿼리 수행 // PK로 인해 Exception이 발생 } catch { // Insert 쿼리 수행 } 하지만 아래와 같이 MERGE 쿼리를 수행하면 더욱 쉽게 구현이 가능하다. 쿼리를.. 2017. 6. 19.
MSSQL 로그인 오류 18456 해결 방법 MSSQL 로그인 시 발생하는 오류 18456 해결 방법입니다.원인은 SQL Server 인증 모드가 비활성화 되어서입니다. 1. 아래와 같이 로그인 시 로그인 오류가 발생한 화면입니다. 오류메세지 : 사용자가 로그인하지 못했습니다.(Microsoft SQL Server, 오류 : 18456) 2. 윈도우즈 계정으로 로그인을 하고, 서버를 선택 후 팝업 메뉴에서 속성을 클릭합니다. 3.서버정보 화면에서 보안을 선택하고, 서버인증을 SQL Server 및 Widnows 인증모드를 선택 후 확인 버튼을 클릭 후 다시 로그인하면 ㄷ해결됩니다. 2017. 3. 5.
MS-SQL DB Suspect 해결법 아래의 쿼리를 수행하면 해결됩니다. -- DB 상태 초기화 EXEC sp_resetstatus 'DBName' -- 긴급 모드로 설정 ALTER DATABASE DBName SET EMERGENCY -- 정합성 체크 DBCC CHECKDB ('DBName') -- 단일 사용자 모드로 설정 ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- 정합성 체크 DBCC CHECKDB ('DBName', REPAIR_ALLOW_DATA_LOSS) -- 사용자 접근 허용 ALTER DATABASE DBName SET MULTI_USER 2016. 11. 11.