본문 바로가기

mssql35

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용 ODBC 생성하기 오라클에서 MS-SQL을 DB Link를 걸기 위해서는 MS-SQL용 ODBC를 생성해야 하는 경우가 있습니다.이럴때 사용하기 위해서 ODBC를 생성하는 방법을 알아보겠습니다. 1. ODBC 데이터 원본 관리자를 실행합니다. - 윈도우즈 10에서는 시작버튼 옆의 검색(돋보기)를 클릭하고, ODBC를 입력하면 해당 프로그램이 표시됩니다. 2. ODBC 데이터 원본 관리자에서 추가버튼을 클릭합니다. 3. SQL Server를 선택하고 마침 버튼을 클릭합니다. 4. ODBC이름과 설명 서버 정보를 입력하고 다음을 클릭합니다. 예) 서버 : 127.0.0.1 5. 인증은 SQL Server 인증 사용을 선택하고, 로그인 ID와 암호를 입력 후 다음을 클릭합니다. 6. MS-SQL의 데이터베이스를 지정합니다. 7.. 2018. 12. 5.
MSSQL 스키마(소유자) 변경하기. MSSQL에서 스키마(소유자)를 변경하는 방법입니다.아래의 Alter문은 하나의 테이블의 스키마를 변경하는 방법입니다. ALTER SCHEMA [변경할 스키마명] TRANSFER [기존 스키마명].[변경할 테이블명] ex) ALTER SCHEMA SchemaName TRANSFER dbo.TableName 2018. 9. 19.
C#의 GUID & MSSQL의 Uniqueidentifier 생성하기 C#의 GUID와 MSSQL의 Uniqueidentifier 생성하는 방법입니다. 1. C#에서 GUID 생성하기 // 빈 GUID Guid emptyID = Guid.Empty; // 새 GUID Guid newID = NewGuid(); 2. MSSQL에서 GUID(Uniqueidentifier) 생성하기 SELECT NEWID() 3. Visual Studio에서 GUID 생성하기 (1) Visual Studio의 도구 메뉴 중 GUID 만들기를 클릭한다. (2) GUID 만들기 화면에서 GUID를 생성하거나 복사 할 수 있다. 2018. 2. 22.
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].[Tab.. 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 JDBC 다운로드 방법 Java로 MSSQL에 접속하려면 MS에서 제공하는 JDBC를 사용해야 합니다.다운로드 URL은 아래와 같습니다. Microsoft JDBC Driver 6.0 for SQL Server 다운로드 아래의 화면에서 다운로드 버튼을 클릭하면 압축파일 또는 실행파일을 다운로드 할 수 있습니다. 아래와 같이 압축을 해제하면 JRE7과 JRE8의 JDBC jar를 있으면, 기본적인 예제는 samples 폴더에 있습니다.개발에 참고하세요. 2017. 3. 5.
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.
MS-SQL 날짜 컬럼 Between MS-SQ에서 날짜 컬럼을 Where(조건절)에서 Between하는 방법입니다. SELECT * FROM TableName where DateColumn BETWEEN '2016/11/08' and '2016/11/08 23:59:59.999' 2016. 11. 11.
MSSQL 쿼리 결과 XML로 변환하기 MSSQL에서 쿼리를 수행 후 나온 결과를 XML로 활용 할 때 사용하면 좋은 Tip입니다. 1. XML Node에 각 컬럼을 Attribute로 변환 - SELECT 쿼리 마지막에 FOR XML RAW('NodeName')를 하면 XML로 변환된다. [쿼리] SELECT * FROM INFORMATION_SCHEMA.TABLES FOR XML RAW('TABLE') [결과] 2. XML Root 지정하여 변환하기 - SELECT 쿼리 마지막에 FOR XML RAW('NodeName'), ROOT('RootName')를 하면 XML로 변환된다. [쿼리] SELECT * FROM INFORMATION_SCHEMA.TABLES FOR XML RAW('TABLE'), ROOT('TABLES') [결과] 3. .. 2016. 10. 27.
MSSQL UniqueIdentifier형 컬럼을 문자형으로 변환 UniqueIdentifier은 ID 컬럼 등에 활용하며, 값은 '00113E4C-0000-0000-0A31-FA8DFA551F04'와 같이 표시됩니다. UniqueIdentifier형을 문자형을 변환은 CONVERT 함수를 이요하면 됩니다. 아래의 쿼리를 참고하세요. -- id는 UniqueIdentifier형의 컬럼입니다. CONVERT(VARCHAR(36), id) 2016. 10. 27.
MSSQL 특정 문자 갯수 MSSQL에서 특정 문자의 갯수를 알 수 있는 쿼리입니다. 아래의 쿼리는 C:\WINDOWS\System32 문자 중 '\'의 갯수 찾는 쿼리입니다. SELECT LEN('C:\WINDOWS\System32') - LEN(REPLACE('C:\WINDOWS\System32', '\', '')) 2016. 8. 31.
MSSQL - 테이블, 뷰, 컬럼 정보 확인하기 MSSQL에서 테이블, 뷰, 컬럼 정보를 확인하는 쿼리입니다. -- Table 정보 SELECT * FROM INFORMATION_SCHEMA.TABLES -- View 정보 SELECT * FROM INFORMATION_SCHEMA.VIEWS -- Column 정보 SELECT * FROM INFORMATION_SCHEMA.COLUMNS 2016. 5. 13.
MSSQL DateTime을 특정 Format 문자로 변경하기 아래의 쿼리는 MSSQL에서 DateTime을 여러 Format 문자로 변경하는 쿼리입니다. 쿼리를 실행하면 여러 Format에 대한 정보를 확인 할 수 있습니다. 이것으로 자신이 필요한 Format으로 사용하면 될 듯 합니다. 참고 : https://msdn.microsoft.com/ko-kr/library/ms187928(v=sql.120).aspx [쿼리] SELECT 10, 'mm-dd-yy', CONVERT(VARCHAR, GETDATE(), 10) UNION SELECT 11, 'yy/mm/dd', CONVERT(VARCHAR, GETDATE(), 11) UNION SELECT 12, 'yymmdd', CONVERT(VARCHAR, GETDATE(), 12) UNION SELECT 23, 'y.. 2016. 2. 25.