요정지니의 램프속 IT세상

 SELECT 
    REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,1234567890),1),'.00','')

     결과값          
     1,234,567,890

출처 : http://kokun.tistory.com/entry/MSSQL-%EC%A1%B0%EA%B1%B4%EB%B6%80-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B3%80%EA%B2%BD-MERGE

SQL Server 2008부터 제공되는 MERGE 문은 하나의 문장에서 경우에 따라서

INSERT, UPDATE, DELETE를 수행할 수 있는 구문이다.​

​

​

​MERGE 구문의 활용을 연습해 보자. 이번 예의 ​간단한 시나리오는 다음과 같다.

​멤버 테이블(memberTBL)에는 기존 회원들이 있는데, 이 멤버 테이블에 직접 INSERT, DELETE, UPDATE를 사용하면 안 된다.

그리고 회원의 가입, 변경, 탈퇴가 생기면 변경 테이블(changeTBL)에 INSERT 문으로 회원의 변경 사항을 입력한다.

변경 테이블의 변경 사항은 신규가입/주소변경/회원탈퇴 3가지라고 가정하겠다.

​

반경 테이블의 작성이 완료된 후에, MERGER 구문으로 변경 테이블의 내용이 '신규가입' 이면 멤버 테이블에 새로 회원을 등록해주고,

'주소 변경' 이면 멤버 테이블의 주소를 변경하고, '회원 탈퇴' 면 멤버 테이블에서 해당 회원을 삭제하게 SQL 문을 작성해 보자.

​

​

​1. 우선 멤버 테이블(memberTBL)을 정의하고, 데이터를 입력해 보자. 지금은 연습 중이므로

   기존 userTBL에서 아이디, 이름, 주소만 가져와서 단단히 만들겠다.​

​

​SELECT userID, name, addr INTO memberTBL FROM userTbl ;

SELECT * FROM memberTBL ;

​

​

​

​

​2. 변경 테이블(changeTBL)을 정의하고 데이터를 입력해 보자. 1명의 신규가입, 2명의 주소 변경, 2명의 회원 탈퇴가

   있는 것으로 가정해 보자.

​

CREATE TABLE changeTBL

​( changeType NCHAR(4) ,  -- 변경 사유

​  userID char(8) ,

​  name nvarchar(10) ,

​  addr nchar(2) ) ;

​GO

​INSERT INTO changeTBL VALUES

​('신규가입', 'TPN', '티파니', '미국') ,

​('주소변경', 'LSG', null, '제주') ,

​('주소변경', 'LJB', null, '영국') ,

​('회원탈퇴', 'BBK', null, null) ,

​('회원탈퇴', 'SSK', null, null) ;

​

​3. 이제는 변경사유(chageType) 열에 의해서 기존 멤버 테이블의 데이터를 변경한다. 5개 행이 영향을 받을 것이다.

​

MERGE memberTBL AS M   -- 변경될 테이블 (target 테이블)

​USING changeTBL AS C   -- 변경할 기준이 되는 테이블 (source 테이블)

​ON M.userID = C.userID   -- userID를 기준으로 두 테이블을 비교한다.

​-- target 테이블에 source 테이블의 행이 없고, 사유가 '신규가입'이라면 새로운 행을 추가한다.

​WHEN NOT MATCHED AND changeType = '신규가입' THEN

​INSERT (userID, name, addr) VALUES(C.userID, C.name, C.addr)

​-- target 테이블에 source 테이블의 행이 있고, 사유가 '주소변경'이라면 주소를 변경한다.

​WHEN MATCHED AND changeType = '주소변경' THEN

​UPDATE SET M.addr = C.addr

​-- target 테이블에 source 테이블의 행이 있고, 사유가 '회원탈퇴'라면 해당 행을 삭제한다.

​WHEN MATCHED AND changeType = '회원탈퇴' THEN

​DELETE ;

​

​4. 멤버 테이블을 조회해 보자. 계획대로 1개 행이 추가되고, 2개 행은 삭제, 2개 행은 주소가 변경되었다.

​

SELECT * FROM memberTBL ;

​

​

출처 : http://bestdev78.tistory.com/50

에러 메시지 

: 

운영 체제 오류 코드 3(지정된 경로를 찾을 수 없습니다.)

위와 같은 에러를 뱉으면서 bulk insert가 안 될 경우가 있다.

해당 문제는 DB와 bulk insert하는 프로그램이 같은 서버에 존재 해야 한다.

bulk insert하는 프로그램이 DB와 다른 서버에 존재 할 경우 위와 같은 에러를 뱉어낸다.

프로그램 뿐만 아니라 쿼리 분석기에서 직접 bulk insert를 하더라도 DB와 같은 서버에서 쿼리 분석기로 명령을 보내야 한다.

한시간이나 투자한 삽질...

출처 : http://www.overtop.co.kr/226

CLR을 사용하는 사용자데이터베이스를 이전 후 CLR 의 함수들을 호출하면 오류가 발생한다.

[오류메세지]

메시지 10314, 수준 16, 상태 11, 줄 6

어셈블리 ID 65536을(를) 로드하는 동안 Microsoft .NET Framework에서 오류가 발생했습니다. 서버의 리소스가 부족하거나 어셈블리가 PERMISSION_SET = EXTERNAL_ACCESS 또는 UNSAFE로 트러스트되지 않은 것 같습니다. 쿼리를 다시 실행하거나 설명서에서 어셈블리 트러스트 문제를 해결하는 방법을 참조하십시오. 이 오류에 대한 자세한 내용은 다음을 참조하십시오:

System.IO.FileLoadException: 파일이나 어셈블리 'valiatephonenumbers, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' 또는 여기에 종속되어 있는 파일이나 어셈블리 중 하나를 로드할 수 없습니다. 보안 관련 오류가 발생했습니다. (예외가 발생한 HRESULT: 0x8013150A)

[해결책]

You may receive an error message when you try to run an existing CLR object or create an assembly that has the external_access or unsafe permission set on a database that is attached or restored from a different server

https://support.microsoft.com/en-us/kb/918040

[설명]

CLR 의 권한중 외부엑세스(Extername_Acess) or 제한없음(UnSafe) 로 설정되어 있는 데이터베이스를 다른 서버로 이전 후 CLR을 호출하면 오류가 발생한다.

오류가 2가지 발생하는 듯하다.

1. SET TRUSTWORTHY ON 옵션상태인 데이터베이스를 백업하거나 COPY해서 복원을 하게되면 OFF 상태로 복원이 된다. 신기하다. ㅋㅋ

2. 데이터베이스 소유자가 틀리면 오류가 발생한다.

USE [CLRTest]

GO

EXEC sp_changedbowner 'sa'

위 2가지를 확인하여 트러블슈팅을 하면 될 것 같다.

#. Case

ALTER ASSEMBLY [ValiatePhoneNumbers]

WITH PERMISSION_SET = EXTERNAL_ACCESS;

/*

OR

CREATE ASSEMBLY ValiatePhoneNumbers from 'E:\SQLData9\ValiatePhoneNumbers.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

*/

/*

메시지 10327, 수준 14, 상태 1, 줄 12

어셈블리 'ValiatePhoneNumbers'에 PERMISSION_SET = EXTERNAL_ACCESS에 대한 권한이 없으므로 어셈블리

'ValiatePhoneNumbers'에 대한 ALTER ASSEMBLY가 실패했습니다. 어셈블리는 DBO(데이터베이스 소유자)에게 EXTERNAL ACCESS ASSEMBLY 권한이 있고 데이터베이스에 TRUSTWORTHY 데이터베이스 속성이 있는 경우 또는 어셈블리가 현재 인증서로 서명되어 있거나 EXTERNAL ACCESS ASSEMBLY 권한이 있는 관련 로그인을 소유한 비대칭 키로 서명되어 있는 경우에 권한이 부여됩니다.

*/

[해결]

// EXTERNAL_ACCESS or unsafe 로 설정을 하기 위해서는 아래와 같은 옵션 이어야 가능하다.

ALTER DATABASE CLRTest SET TRUSTWORTHY ON;

[참고문서]

TRUSTWORTHY 데이터베이스 속성

https://msdn.microsoft.com/ko-kr/library/ms187861(v=sql.120).aspx

출처: http://www.overtop.co.kr/226 [SQLin]

출처: http://duu.co.kr/367

숫자 정렬시 1, 10, 11, 12 ..... 2, 20 순으로 나온다

이것을 1, 2, 3, 4 순으로 정렬하려면?

select * from 테이블명 ORDER BY LEN(컬럼명) ASC, 컬럼명 ASC