bulk insert

--0. ######## DB에임시테이블생성########
CREATE TABLE [dbo].[tbl_Test](
       [seq] [varchar](50) COLLATE Korean_Wansung_CI_AS NULL,
       [oldnm] [varchar](50) COLLATE Korean_Wansung_CI_AS NULL,
       [newnm] [varchar](50) COLLATE Korean_Wansung_CI_AS NULL
) ON [PRIMARY]

--1. ######## DB에SP생성##################
Create PROC usp_XML_Test
    @RegistXML xml
AS
begin
INSERT INTO [Temp].[dbo].[tbl_Test]
           ([seq]
           ,[oldnm]
           ,[newnm])
SELECT
 M.Item.query('./SEQ').value('.','VARCHAR(50)') SEQ,
  M.Item.query('./OLDNM').value('.','VARCHAR(50)') [OLDNM],
  M.Item.query('./NEWNM').value('.','VARCHAR(50)') [NEWNM]
FROM @RegistXML.nodes('/ROOT/INFO') AS M(ITEM)
end
--2. ######## 외부 웹서버로부터외부 xml정보받아와서SP호출##################
exec usp_XML_Test
'<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
   <INFO>
     <SEQ>1</SEQ>
     <OLDNM>a군</OLDNM>
     <NEWNM>x군</NEWNM>
   </INFO>
   <INFO>
     <SEQ>2</SEQ>
     <OLDNM>b군</OLDNM>
     <NEWNM>b군</NEWNM>
   </INFO>
   <INFO>
     <SEQ>3</SEQ>
     <OLDNM>c군</OLDNM>
     <NEWNM></NEWNM>
   </INFO>
</ROOT>''<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
   <INFO>
     <SEQ>1</SEQ>
     <OLDNM>a군</OLDNM>
     <NEWNM>x군</NEWNM>
   </INFO>
   <INFO>
     <SEQ>2</SEQ>
     <OLDNM>b군</OLDNM>
     <NEWNM>b군</NEWNM>
   </INFO>
   <INFO>
     <SEQ>3</SEQ>
     <OLDNM>c군</OLDNM>
     <NEWNM></NEWNM>
   </INFO>
</ROOT>'
--3. ######## 결과확인##################
select * from dbo.tbl_Test

MS-SQL 테이블 조회

테이블 명이 tbl_로 시작하면...

SELECT distinct(table_name ) FROM INFORMATION_SCHEMA.COLUMNSwhere table_name like 'tbl_%'

MS-SQL 접근 IP조회

select c.client_net_address
,c.local_net_address , A.hostname, A.loginame, B.name, A.program_name
from master..sysprocesses A
, sys.databases B
, sys.dm_exec_connections c
where A.dbid = B.database_id
and
A.sql_handle = c.most_recent_sql_handle
and client_net_address not in('')
group by c.client_net_address
,c.local_net_address , A.hostname, A.loginame, B.name, A.program_name

tempdb 초기화

backup log tempDB with no_log
DBCC SHRINKDATABASE(N'tempdb' )

다른 디렉토리로 변경
http://www.dator.co.kr/?document_srl=65617&tags=Database

Oracle 쿼리 로그

select * from v_$sql where sql_text like '%table%'

TRIGGER일때 자동 증가값 조회

GO
/****** 개체: Trigger [dbo].[tr_del] 스크립트 날짜: 08/20/2011 19:52:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create TRIGGER [dbo].[tr_del]
ON [dbo].[LicIssueInfo]
FOR INSERT
AS
DECLARE @idx int
DECLARE @strSql nvarchar(4000)
DECLARE @subSql nvarchar(4000)

SELECT @idx = [idx]
FROM inserted
SET @strSql = N'CREATE TABLE [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] (
[idx] [int] NOT NULL ,
[pckidx] [int] NULL ,
[userID] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
[userIP] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userMac] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userLoc] [char] (2) COLLATE Korean_Wansung_CI_AS NULL ,
[issueDate] [datetime] NULL
) ON [PRIMARY]'
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END

ELSE

BEGIN
EXEC sp_executesql @strSql
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END
END

insert 자동 증가 값 조회

DECLARE @strSql nvarchar(4000)
DECLARE @strSql2 nvarchar(4000)

SET @strSql = N'CREATE TABLE [dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +'] (
[Idx] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[uid] int
) ON [PRIMARY]'


BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
---테이블이 존재하면,
BEGIN
---수험번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') + '_' + @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = @@Identity

END
ELSE
---테이블이 없다면,
BEGIN
---테이블 생성
EXEC sp_executesql @strSql
---수험 번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = 1
END
END