SJ.Lee Dictionary

oracle

create table create_table_name as
select * from select_table_name

mssql

SELECT * INTO create_table_name FROM  (SELECT * FROM selcet_table_name)A

mysql

CREATE TABLE create_table_name AS
SELECT * FROM select_table_name

이번에 다른 DB로 쿼리를 변환 해야 하는 일이 생겼다.

이 무식한 짓을 하려고 별의 별 구상을 다하던 중 툴이 있지 않을까 하고 검색해 보았다.

그래서 발견하게된 "sqlines" 진짜 잘 되려나 싶어 테스트를 해보았다.

온라인 에서 단순 쿼리만 변경 할 수도 있고 로컬에서 특정 쿼리만 또는 sql파일 자체를 변환 할 수 있었다.

오프라인 방법은 우선 해당 툴을 다운 받아준다.

다운 후 압축을 풀면 3가지 버전이 나오게 된다.

1. 콘솔에서 특정 쿼리만 변경

2.프로그램으로 특정 쿼리 변환하기

3. 폴더에 들어있는 쿼리 변경하기

폴더의 경우 경로 설정을 \\\ 등으로 설정할 경우 하단의 모든 폴더의 쿼리를 찾아 변경하는것을 확인했다.

물론 변환 폴더에 동일한 경로로 폴더까지 자동 생성하였다.

계정 생성 및 권한을 줄때 에러가 발생 된다.

보안개체의 사용자 생성 시 각 데이터 베이스의 사용자또한 함께 만들어 지기 때문인대

이대 스크립트를 보면

create "userid" for login "database" 를 하고 grant 작업이 진행이 되면서 각 DB에 사용자를 다시 만들기 때문이다

이때 발생 에러메세지로

제목: Microsoft SQL Server Management Studio "USERID"사용자 'DATABASE'에 대한 만들기이(가) 실패했습니다. (Microsoft.SqlServer.Smo)
추가 정보: Transact-SQL 문 또는 일괄 처리를 실행하는 동안 예외가 발생했습니다.(Microsoft.SqlServer.ConnectionInfo)
현재 데이터베이스에 사용자, 그룹 또는 역할 'GRANT'이(가) 이미 있습니다. (Microsoft SQL Server, 오류: 15023) 

각 DB의 사용자를 보게되면 사용자는 생성 되었지만 권한이 전부 빠져있을 것이다.

해결방법으로는 시스템으로 자동 보정을 해 주면 된다.

EXEC sp_change_users_login 'Auto_Fix', 'USERID'

위 명령어를 각 DB에 접속하여 보정해 준다.

MSSQL 에서 메일 보내기가 가능합니다.

SMTP사용을 위한 메일정보가 필요하며 해당 계정을 MSSQL에 셋팅하여 메일 보내기를 합니다.

1. MSSQL에 메일 정보 셋팅

    - SSMS 로 접속
    - DB 하단의 "관리" 메뉴 하단 "데이터베이스 메일" 더블클릭 후 메일정보 설정 진행

2. DB메일 발송 및 확인

-- 메일 발송

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MSSQL DB Email 설정시 입력한 프로필 이름',
@recipients = '보내고자 하는 이메일 주소',
@subject = '제목',
@body = '내용',
@body_format = 'HTML' or 'TEXT';

-- 발송 내역 확인 성공 or 실패 or 발송 불가
SELECT sent_status, *
FROM msdb.dbo.sysmail_allitems
order by mailitem_id desc;

mssql 에서 활성 중인 또는 실행 대기 중인 쿼리와 세션을 확인 하여 lock에 걸린 문제의 세션을 kill 하기.

--세션
SELECT sqltext.TEXT, req.session_id, req.status, req.start_time, req.command, req.cpu_time, req.wait_time, req.total_elapsed_time, text_size
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by sqltext.TEXT, req.status
;

-- lock
SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes        
FROM    sys.dm_exec_sessions        
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id        
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'DATABASE'
AND request_mode LIKE '%X%'
--AND name ='YG1MES'
ORDER BY name 
;

--kill 은 서버에서
kill session_id
;

컬럼에 identity 속성이 설정되어 있어서 임의적으로 데이터 삽입이 불가능.

insert into table_name

select * from table_name;

처리시 id값이 증가해야 함으로 컬럼명을 지정하지 않은 상태에서 데이터가 들어갈 수 없음.

SET IDENTITY_INSERT table_name ON

insert into table_name

select * from table_name

SET IDENTITY_INSERT table_name OFF

로 임시적으로 허용 후 데이터 처리 다시 테이블의 제약조건을 허용하여 처리가 가능함.

//** 2024.01.18 내용 추가 **//

위 건으로 처리 되지 않음.

SET IDENTITY_INSERT table_name ON

insert into table_name(column1, column2)

select column1, column2  from table_name

SET IDENTITY_INSERT table_name OFF

컬럼을 명시하여 처리해야 오류발생되지 않음.

아래는 mssql error내역에 대한 내용과 처리 관련한 내용을 표기함.

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

To illustrate on how the error can be encountered:

CREATE TABLE [dbo].[Users] (
    [UserID]    INT NOT NULL IDENTITY(1, 1),
    [UserName]  VARCHAR(20) NOT NULL,
    [FirstName] VARCHAR(50) NOT NULL,
    [LastName]  VARCHAR(50) NOT NULL
)
GO

INSERT INTO [dbo].[Users] 
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.Users' can only be specified when a 
column list is used and IDENTITY_INSERT is ON.

If you specified the column names in the INSERT statement, you will get a different error message:

INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Users' when 
IDENTITY_INSERT is set to OFF.

Solution / Workaround:

There are two ways of avoiding any of the errors mentioned above.  The first option is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record:

INSERT INTO [dbo].[Users] ( [UserName], [FirstName], [LastName] )
VALUES ( 'superman', 'Clark', 'Kent' )
GO

The second option is to enable the IDENTITY_INSERT property for the table.  If you really want to specify the value for the identity column, this option is the one for you.

SET IDENTITY_INSERT [dbo].[Users] ON
GO

INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO

SET IDENTITY_INSERT [dbo].[Users] OFF
GO

Setting the IDENTITY_INSERT to ON for the table allows explicit values to be inserted into the identity column of a table.  At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON.  If a table already has this property set to ON and a SET IDENTITY_INSERT ON statement is issued on another table, SQL Server will return the following error message:

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'dbo.Deparments'. 
Cannot perform SET operation for table 'dbo.Users'.

Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.

ms-sql db백업 과 복구

*Backup

bcp db_name.table_name out backup_name.bak -c /U user_name /P user_password

*Restore

bcp db_name.table_name in backup_name.bak -c /U user_name /P user_password

DB.TABLE  특정 테이블 백업을 진행

테이블명을 명시 하지 않을 경우 디비를 통채로 백업 합니다.

bcp 명령어에서 out 옵션으로 백업

bcp 명령어에서 in 옵션으로 복구 진행

복구시에 기존에 들어있는 테이블의 데이터는 없어지며 복구된 데이터만 남습니다.