DataBase/MS-SQL

[MS-SQL] 데이터 마이그레이션을 위한 스크립트 작성 및 오라클에 있는 ROWNUM 만드는 방법

엑셀파일 데이터를 데이터베이스에 INSERT 작업 후 실제로 사용하는 테이블에 데이터를 등록해야할 때 사용하면 유용한 스크립트 작성을 하였습니다. 커서(CURSOR)를 사용하여 반복처리해도 되지만 조회 리스트에 순차적인 번호를 조회시 생성하여 질의한 후 WHILE문을 통해 데이터를 INSERT하는 방법입니다. 이 방법은 기존 데이터는 모두 삭제 처리 후 신규로 밀어넣는다는 조건으로 처리되었습니다. 기존 데이터를 살리면서 업데이트 혹은 INSERT를 해야한다면 MERGE문을 사용하거나 조건을 걸어 UPDATE 또는 INSERT문을 분기하면 됩니다. ROWNUM 만들기 구문(문법) ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] ord..

2022. 1. 3. 16:51
DataBase/MS-SQL

[MS-SQL] 엑셀파일 데이터를 DB에 INSERT 하는 방법(Microsoft SQL Server Management Studio)

유관자로 부터 기존 고객정보를 삭제하고 신규로 등록해달라고 엑셀파일로 요청을 해올 때 데이터베이스에 INSERT 하는 방법에 대해 알아봅니다. DB 작업을 위한 Microsoft SQL Server Management Studio 정보 Microsoft SQL Server Management Studio12.0.6024.0 Microsoft Analysis Services 클라이언트 도구12.0.6024.0 Microsoft Data Access Components(MDAC)10.0.18362.1 Microsoft MSXML3.0 6.0 Microsoft Internet Explorer9.11.18362.0 Microsoft .NET Framework4.0.30319.42000 운영 체제6.3.18363..

2022. 1. 3. 11:22
DataBase/MS-SQL

[MS-SQL] JSON 데이터를 xml데이터로 파싱하는 함수를 사용하여 DB에 저장하는 방법

다음 함수는 JSON데이터 타입으로 프론트엔드쪽에서 넘겨준 JSON데이터 타입의 데이터를 MSSQL에서 XML데이터로 파싱하는 함수 입니다. 테이블 반환 함수는 아니며, 스칼라 반환 함수입니다. 그럼 이런 건 언제 활용하느냐? 프론트엔드쪽에서 테이블구조의 데이터를 JSON 타입으로 데이터를 넘긴 후 DB에 저장할 때 활용하면 굉장히 유용합니다. 보통 아이바티스나 마이바티스와 같은 ORM를 사용과 백엔드 코드를 작성하여 저장하면 코드 구현시 문제될 게 없지만 모든 저장 비즈니스 로직을 프로시저에서 구현해서 저장해야한다면 아주 유용합니다. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION FUN_PARSE_JSON_TO_XML( @json n..

2021. 12. 29. 16:06
DataBase/MS-SQL

[MS-SQL] DB이관시 동적으로(?)INSERT 쿼리 작성하는 방법

이 방법은 10년전 오라클 DBA 보조 업무를 맡고 있을 때 배웠던 방법에 대해 기록합니다. 가령, 테스트 DB에서 실DB로 데이터를 이관할때 사용하면 됩니다. MSSQL 테이블 스키마 및 데이터 모두 SQL 스크립트 생성하는 방법 이 방법은 모든 테이블과 그 테이블들의 모든 데이터를 기준으로 이관할 때 이용하면 매우 편합니다. 전체 테이블과 데이터를 모두 스크립트화하여 SQL파일로 생성할 수 있습니다. 1. MSSQL의 경우 "개체탐색기"에서 DB를 선택 후 마우스 오른쪽 우클릭 2. 팝업메뉴에서 태스크(T) 선택 3. 스크립트 생성(E)를 선택 4. 스크립트 생성 및 게시 마법사 팝업에서 전체를 할지 원하는 테이블만 할지 선택 5. 특정 테이블 선택 후 "고급(A)" 버튼을 클릭합니다. 6. 스크립팅..

2021. 12. 23. 18:25
DataBase/MS-SQL

[MS-SQL] 시간 데이터를 시/분/초 추출하는 방법

예전에 기록하지 않았던 것들에 대한 기록을 조금씩 시작해봅니다. GETDATE()를 이용하여 현재 날짜 시간 정보에서 시간에 대한 정보만 가져오는 방법과 시분초로 쪼개서 가져오는 방법들에 대해 기록합니다. 현재날짜 시간을 가져올 수 있는 함수는 다음과 같은 종류가 있어요. GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), GETUTCDATE() SELECT문으로 조회를 해보면 SELECT GETDATE() AS GETDATE ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP] ,SYSDATETIME() AS SYSDATETIME ,SYSDATETIMEOFFSET() AS SYSDAT..

2021. 12. 15. 17:24
DataBase/MS-SQL

[MS-SQL] DELETE문에 INNER JOIN문 사용하여 삭제하는 활용법

MSSQL 데이터베이스의 DELETE문 사용방법에 대해 알아봅니다. delete문의 경우 데이터베이스에서 데이터를 삭제할 때 사용하는 구문입니다. 그럼으로 주의해야합니다. 테스트DB라면 상관없지만 상용DB라면 큰일이 발생하게 됩니다. 그럼으로 항상 주의해야합니다. 백업DB로 복구하는것도 한계가 있기 때문이에요. delete문을 실행하기 전에 꼭 확인 꼭 확인입니다. 오라클 DBA로 잠깐 일 시작했을 때 100명의 개발자를 2시간 동안 놀게 만든적이 있기 때문에 당부를 드리는거에요. 지금은 웃지만 그 때는 패닉이었어요. 100명의 개발자가 2시간 놀면 그날 허공의로 날린 회사 비용은??? DELETE문과 UPDATE문 사용시 반드시 트랙잰션 구문을 사용해서 처리합니다. 그래야 롤백도 할 수 있고 커밋도 할..

2021. 12. 10. 16:23
DataBase/MS-SQL

[MS-SQL] PIVOT(피봇) 기본 및 심화(동적 쿼리 생성)

피봇(PIVOT)은 질의한 데이터에 대한 행의 집합을 열로 변환시켜 보여줄 때 사용하게 됩니다. 보통의 경우 통계 데이터를 추출하는 쿼리를 작성할 때 많이 사용되지요. 반대로 UNPIVOT은 열을 행으로 보여줄 때 사용해요. 테스트를 위해 테이블 및 데이터를 INSERT 합니다. CREATE TABLE [dbo].[TB_TEST]( [SEQ] [varchar](10) NOT NULL, [USER_NM][varchar](30) NOT NULL, [KOR] [varchar](3) NOT NULL, [MAT] [varchar](3) NOT NULL, [ENG] [varchar](3) NOT NULL, [CRT_DATE] [varchar](8) NOT NULL, CONSTRAINT [PK_TB_TEST] PRI..

2021. 11. 19. 11:59
DataBase/MS-SQL

[MS-SQL] 프로시저를 사용했을 때 in절 사용 방법(iterate 구현하기)

쿼리를 작성시 IN절을 사용하여 여러개의 값을 받아서 일치하는 값들에 대한 리스트 결과를 받아야할 때가 있습니다. 만약 ORM 중에 iBATIS(아이바티스) 혹은 마이바티스를 사용중이라면 쿼리를 구현하는 xml쪽 파일에서 iterate를 사용하여 처리할 수 있습니다. (iBATIS)아이바티스 IN절 사용법 (iterate 사용) [Server side 코드] var idList = new List(); idList.Add(10); idList.Add(12); [아이바티스 내 사용법] .. ddolcat.tistory.com ORM을 사용하고 있지만 단순하게 프로시저를 호출해주는 연결자 역할만 하고 있다면??? 모든 비즈니스 로직이 프로시저에 있는 경우가 있어요. 유지보수의 편의성 때문이겠지요. 서버사이드..

2021. 11. 11. 09:40
DataBase/MS-SQL

[MS-SQL] 특정 기간에 날짜(일, 월, 시간) 생성하는 쿼리를 작성하는 방법은??(MASTER..SPT_VALUES 시스템 테이블에 답이 있다.)

특정 기간 동안 통계 자료를 뽑을 때 , 혹은 차트 도표의 자료의 데이터를 추출할 때 월별 혹은 일자별로 뽑아야하는 경우가 있습니다. 모든 데이터가 존재하면 문제가 되지 않지만, 특정 날짜가 누락되거나, 특정 월의 데이터가 없을 경우, 통계 쿼리 결과가 그 월 혹은 일은 빠지기 때문에 차트에 표기하거나 도표에 보여줄때 데이터가 없는경우 표기하지않는 경우가 생깁니다. 이럴 때는 어떻게 해야할까요? 매년 1월부터 12월까지 달력정보를 특정 테이블에 INSERT 한 후 가져오는 방법도 있으나 이 방법은 매년 달력 일 정보를 INSERT 해줘야하는 불편함이 생깁니다. 뭐 미리 5년치 INSERT 하신다면 상관없으려나... 그것보다는 특정 기간 동안의 일 또는 월을 모두 생성하는 쿼리를 만들면 수월해집니다. MS..

2021. 11. 8. 14:46
DataBase/SQLite

안드로이드 SQLite 검색문 예시( SELECT 문)

나중에 활용하기 위해 기록을 남긴다. 사람의 기억력은 그리 오래가지 못한다. 내가 개발하고도 일주일 지나면 내가 개발한 코드가 맞나? 싶을때가 가끔있다. 한 달이 지나면 그 기억은 더 쇠퇴한다. 그러하니 기록을 남기자. 그나마 불안감이 사라질테니까..... [스크립트1] public static Cursor selectMyNumberList(SQLiteDatabase database, String searchParam ){ String queryParam; queryParam = "SELECT event_id " + ", _event_date" + ", _my_number_1" + ", _my_number_2" + ", _my_number_3" + ", _my_number_4" + ", _my_numbe..

2020. 4. 28. 03:01
DataBase/SQLite

안드로이드 SQLite 통계쿼리문 작성예시

개발을 하다보면 통계정보가 필요할 경우가 많다. 이러한 경우 DB에서 데이터를 조회하는데 내가 원하는 데이터에 맞게 스크립트를 작성해야한다. 나중에 기억나지 않을 것을 대비하여 기록을 남긴다. [스크립트1] public static Cursor selectData(SQLiteDatabase database, String tabId){ String pDate =""; if("tab1".equals(tabId)) { pDate = DateUtil.getCurrentDate("yyyy-MM-dd"); }else if("tab2".equals(tabId)) { //Calendar cal = new GregorianCalendar(); Calendar cal = Calendar.getInstance(); //ca..

2020. 4. 28. 02:54
DataBase/SQLite

안드로이드 SQLite 데이터 변경 방법(UPDATE문)

SQLite를 사용시 등록한 데이터에 대한 수정(변경)을 위해 update문을 사용한다. 입력한 정보중에 잘 못 입력하였거나 변경이 필요한 경우 사용하게 된다. 여러개의 칼럼을 동시에 업데이트 할수 있으며, 하나의 칼럼만 업데이트 할 수 도 있다. update set where를 기억하자. [샘플 스크립트1] public static void updateTable(SQLiteDatabase database, int gubunType, int eventID) { if (database != null) { try { database.execSQL("UPDATE TB_FINEDUST_RECORD " + "SET " + " _event_type =" + gubunType + " WHERE event_id =" ..

2020. 4. 28. 01:53
DataBase/SQLite

안드로이드 SQLite 데이터 추가 방법(INSERT문)

테이블에 데이터를 추가하는 스크립트는 다음과 같다. 샘플스크립트 여러개 올려두니 참고하자. 데이터를 추가하기 위해서는 INSERT문을 사용하게된다. public static void insertTable(SQLiteDatabase database, int gubunType, String sDate){ if(database != null){ try { String currentDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar calendar = Calendar...

2020. 4. 28. 01:44
DataBase/SQLite

안드로이드 SQLite 데이터 삭제 방법(DELETE 문) : 총정리

SQLite 사용시 테이블 삭제 스크립트는 다음과 같다. 테이블의 데이터를 삭제하고 할 경우 Delete문을 사용하여 삭제가 가능하다. SQLIte는 Truncate 문을 지원하지 않는다. 그럼으로 모든 데이터를 삭제하는 방법뿐이다. 그러나 저러나 왜 Truncate문을 지원하지 않는걸까?? ■샘플 스크립트1 : 테이블의 PK값을 기준으로 삭제하는 방법 public static void deleteTbMyNumber(SQLiteDatabase database, int id){ database.execSQL("DELETE FROM TB_LOTTO_MY_NUMBER WHERE event_id =" + id ); } ■샘플 스크립트2 : 테이블 전체 데이터를 삭제하는 방법 public static void d..

2020. 4. 28. 01:36
DataBase/SQLite

안드로이드 SQLite 테이블 생성 방법(CREATE TABLE)

테이블 생성방법은 다음과 같다. Create table문을 사용하여 테이블을 생성한다. 테이블 생성시 integer칼럼에 대해 데이터 추가시 자동으로 증가하도록 테이블 생성시 적용하였다. public static void createTablehistory(SQLiteDatabase database){ try { if(database != null){ database.execSQL("CREATE TABLE if not exists TB_LOTTO_WINNER_NUMBER (" + "event_id integer PRIMARY KEY autoincrement" +",_event_round integer" // 당첨회차 +",_event_date VARCHAR(10)"// 당첨일자 yyyy.mm.dd +",_..

2020. 4. 28. 01:20
728x90