본문 바로가기

MS-SQL

[MS-SQL] 데이터 중복 삭제 쿼리 데이터 중복삭제 쿼리 sequence를 잡아준 후에 사용하는게 좋음. DELETE tblTest WHERE seq IN (SELECT T1.seq FROM tblTest T1 ,tblTest T2 WHERE t1.seq < t2.seq AND t1.fileA = t2.fileA and t1.fileB = t2.fileB) 더보기
[MS-SQL] 에러 처리 방법 SET XACT_ABORT ON BEGIN TRY BEGIN TRAN INSERT A VALUES ( '1') COMMIT TRAN print 'Success' END TRY BEGIN CATCH ROLLBACK print 'Fail' END CATCH 더보기
[MS-SQL] MD5, sha1 암호화 * MD5 암호화 SELECT (SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','ollagaza')),3,32)) * sha1 암호화 SELECT (SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('SHA1','ollagaza')),3,32)) 더보기
[MS-SQL] Merge 기능 MS-SQL 2008이상에서는 Merge라는 기능이 생겼는데 이 기능은 구조가 같은 두개의 테이블을 하나로 합쳐주는 기능이다. 이것 저것 테스트해봤는데 일단 이 기능은 Master DB에서밖에 사용이 안되는 것 같다. 마스터에서 타 DB 호출하여 사용가능하고 Linked로 연결된 서버는 Merge 기능이 작동하지 않는걸 확인했다. 하지만 내부 서버라면 유용하게 사용이 가능하다. -- 테스트 임시 테이블 생성 DECLARE @MERGE_TABLE1 TABLE ( LOG_NO INT IDENTITY(1,1), CODE VARCHAR(50), MESSAGE VARCHAR(2000)) DECLARE @MERGE_TABLE2 TABLE ( LOG_NO INT IDENTITY(1,1), CODE VARCHAR(50.. 더보기
[MS-SQL] 현재 실행중인 쿼리 보기 현재 실행중인 쿼리 보기 SELECT dm_ws.wait_duration_ms 실행대기시간, dm_ws.wait_type 대기형태, dm_es.status 실행상태, dm_t.TEXT 실행쿼리, dm_qp.query_plan 실행계획, dm_ws.session_ID 세션아이디, dm_es.cpu_time , dm_es.memory_usage , dm_es.logical_reads 논리적읽기, dm_es.total_elapsed_time 총실행시간, dm_es.program_name , DB_NAME(dm_r.database_id) DB명, --Optional columns dm_ws.blocking_session_id blk, dm_r.wait_resource, dm_es.login_name, dm_r... 더보기
[MS-SQL] Text / CSV 파일 DB 업로드 가끔 작업하다보면 대용량 파일을 DB에 업로드할 일이 생기는데 이럴때 유용하게 사용이 가능할 것 같다. 실제 작업시에 csv 파일이 255MB 였는데 엑셀로 열면 특수문자나 외국어가 많아서 깨지고 난리가 나고 행이 길어서 엑셀에서는 전체 행이 표시가 되지도 않음 아래 쿼리로 실행하면 깔끔하게 업로드가 가능하다. BEGIN TRANSACTION -- DROP TABLE tblCSV CREATE TABLE tblCSV ( fld1 nvarchar(200 ) NULL, fld2 nvarchar(200 ) NULL, ) BULK INSERT tblCSV FROM 'c:\file.csv' -- 서버의 실제 파일 경로 WITH ( fieldterminator = ',' , -- 컬럼 구분 문자열 rowtermina.. 더보기
[MS-SQL] 저장프로시져(Stored Procedure) 암호화 하기 타 업체에 부득이하게 stored procedure도 같이 넘겨줘야 할때 프로시져를 암호화 해서 내용을 확인 불가하게 만들자!! CREATE PROCEDURE spTest WITH ENCRYPTION AS INSERT INTO tblTest(cla, clb) values ('1','2') GO * 생성시 WITH ENCRYPTION 옵션을 넣어주면 추후에 해당 프로시져를 열어볼 수 없게 된다.EXEC SP_HELPTEXT 'spTest' 를 실행해보면 개체 'spTest'의 텍스트가 암호화되었습니다. 라는 메세지를 보게 된다. 한번 암호화 되면 Third-party 를 따로 구해서 해독해야 하므로반드시 암호화 하기 전에 해당 프로시져를 따로 백업해놓아야 한다. [복호화툴]http://www.devlib... 더보기
[MS-SQL] row_number() over 사용하기 -- 특정 그룹별로 일련번호 select groupCode, row_number() over(PARTITION BY hCode order by groupCode desc) as rowNo from ollagaza * RESULT A 1 A 2 A 3 A 4 B 1 B 2 C 1 C 2 C 3 -- 전체 일련번호 select groupCode, row_number() over(order by groupCode desc) as rowNo from ollagaza where hCode LIKE 'JPTYO%' * RESULT A 1 A 2 A 3 A 4 B 5 B 6 C 7 C 8 C 9 더보기
[MS-SQL] 문자열 위치 찾기 * 문자열 위치 찾기 * ASP inStr과 유사한 함수 patindex('%찾을문자열%','전체문자열') select patindex('%olla%','ollagaza') -- 실행하면 찾을문자열이 몇번째 위치에 있는지 출력, 없으면 0 더보기
[MS-SQL] 특정 문자열부터 이후 내용 제거하기 Injection 공격을 당했을 때 사용하는 방법인데.. text 는 해당 내용 하지 말 것. UPDATE tableName SET email=replace(cast(email as varchar(8000)),substring(cast(email as varchar(8000)),charIndex('',email),len(cast(email as varchar(8000)))),'') WHERE charIndex('',email) > 0 더보기
[MS-SQL] IDENTITY 자동증가값 초기화 DBCC CHECKIDENT (테이블명, RESEED , 0) 더보기
[MS-SQL] 테이블의 필드명 변경 sp_rename '테이블명.기존필드명' , '바꿀필드명' 더보기
[MS-SQL] 패스워드 변경하기 Alter login ollagaza with password ='1111' old_password='1234' EXEC sp_password NULL, '1111' ,'ollagaza' 더보기
[MS-SQL] 특정 필드명을 사용하는 테이블 찾기 SELECT table_name, * FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name = '필드명' 더보기
[MS-SQL] IDENTITY 속성 주기/해제 MS-SQL을 사용하다 보면 Identity 값 때문에 Insert라던지 문제가 생길 경우가 있는데 이때는 Identity 속성을 임시로 없애주고 다시 잡아줄 수 있다.. SET IDENTITY_INSERT tbl ON INSERT INTO tbl (seq, names) VALUES (12, 'justin') GO 작업이 완료되었으면 IDENTITY_INSERT을 다시 OFF으로 설정하고 IDENTITY 속성이 정상적으로 동작하는지 확인 SET IDENTITY_INSERT tbl OFF INSERT INTO tbl (seq, names) VALUES (13, 'justin') GO 하면 Error가 발생하면 OK 더보기
[MS-SQL] prepared statement 쿼리 ' 일반쿼리 사용시 SQL = "select * From tblGroupSpecial WHERE category='" & category & "' ORDER BY specialName" Set Rs = Server.CreateObject("ADODB.Recordset") Rs.Open SQL, dbCon If Not(Rs.eof) Then Do Until Rs.eof If Rs("specialTitle") = "" Then specialMsg = Rs("specialName") Else specialMsg = Rs("specialTitle") End IF Rs.movenext Loop End If Rs.close Set Rs = Nothing ' prepared statement 쿼리 사용시 '// 커맨.. 더보기
[MS-SQL] Split Function split를 테이블로 반환해 준다. CREATE FUNCTION dbo.spSplit ( @StrValue VARCHAR(1000), -- 분리할 문자열 @SplitChar VARCHAR(1) -- 구분할 문자 ) RETURNS @SPLIT_TEMP TABLE ( VALUE VARCHAR(50) ) AS BEGIN DECLARE @oPos INT, @nPos INT DECLARE @TmpVar VARCHAR(1000) -- 분리된 문자열 임시 저장변수 SET @oPos = 1 -- 구분문자 검색을 시작할 위치 SET @nPos = 1 -- 구분문자 위치 WHILE (@nPos > 0) BEGIN SET @nPos = CHARINDEX(@SplitChar, @StrValue, @oPos ) IF @nPos.. 더보기
[MS-SQL] 테이블 복사하기 select * into 신규테이블 from 원본테이블 단, primary key나 제약조건등은 복사되지 않으니 주의해야함 더보기
[MS-SQL] Dead lock 방지하기 쿼리를 사용하다 보면 동시에 Insert, Update, Delete가 실행되는 경우가 많은데 이 때 Dead lock를 방지하기 위한 방법 SELECT * FROM tblMember with(nolock) WHERE uid'' 혹은 프로시져 상단에 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 같이 써주면 된다. 더보기
[MS-SQL] 테이블 존재 유무 체크 ******** 1 if exists(select * from information_schema.tables where table_name='tblOllagaza') BEGIN DROP TABLE tblOllagaza select N'삭제' END ELSE BEGIN select N'없음' END ******** 2 select * From sysobjects where name='tblOllagaza' 데이터베이스에 테이블이 존재하는지 체크. 더보기
반응형

티스토리툴바