한방인서트 insert

                

declare @OutResult int

EXEC dbo.prAddLogItemTest  '2010-03-01 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보†2010-02-25 16:25:01|1|101|201|0|123|latale|천사1004|192.168.2.50|00-00|000-000000-000000|tolatale|악마|111.111.111.111|기타정보'

        , '|'

        , '†'

        , 10

        , @OutErrorCode =  @OutResult OutPut

print @OutResult

Return                 :  0 (정상처리), -20001 (아이템로그 입력처리 오류)

*/

ALTER PROC [dbo].[prAddLogItemMulti]

    @LogText varchar(max),

    @FieldValue varchar(10),

    @LineValue varchar(10),

    @RowCnt int,

    @OutErrorCode int output

AS

BEGIN

SET NOCOUNT ON 

    DECLARE @LogStr nvarchar(max);

    

    SELECT @LogStr = 'INSERT INTO dbo.LogItem (LogTime, ServerID, GameServerNo, LogType, ItemID, SerialKey, AccountID, CharName, IPAddr, MapGroupID, MapPosition, ToAccount, ToChar, ToIPAddr, Etc) SELECT ''' + REPLACE(REPLACE(SUBSTRING(@LogText,1,LEN(@LogText)-LEN(@LineValue)),@FieldValue,''','''),@LineValue,''' UNION ALL SELECT ''') + '''';

    EXEC sp_executesql @LogStr;

IF @@ERROR = 0

SET @OutErrorCode = 0;

ELSE

SET @OutErrorCode = -20001;

END

create table test_row_multi_insert (

idx int not null identity(1,1) primary key

, getTime datetime not null

, serverid int not null

, type varchar(2) not null

, name varchar(10) not null

);

/*

alter table test_row_multi_insert

add constraint cl_idx primary key (idx);

*/

/*

create table test_row_multi_insert (

idx int not null identity(1,1) primary key

, getTime datetime not null

, serverid int not null

, type varchar(2) not null

, name varchar(10) not null

, constraint cl_idx primary key (idx)

);

*/

alter procedure test_row_mulit_insert

@rowText nvarchar(max)

, @FieldValue varchar(10)

, @LineValue varchar(10)

, @RowCnt int

, @outErrorCode int output

as

begin

set nocount on;

declare @tmpStr nvarchar(max);

select @tmpStr = 'INSERT INTO dbo.test_row_multi_insert (getTime, serverid, type, name) SELECT ''' + 

REPLACE(REPLACE(SUBSTRING(@rowText, 1, (LEN(@rowText) + 1)-LEN(@LineValue)), @FieldValue, ''','''), @LineValue, ''' UNION ALL SELECT ''') + '''';

exec sp_executesql @tmpStr;

if @@error = 0

set @outErrorCode = 0;

else

set @outErrorCode = -1;

end;

declare @OutResult int

exec dbo.test_row_mulit_insert '2014-11-01 17:11:11|1|A|홍길동†2014-11-02 17:11:11|1|B|백두산†2014-11-03 17:11:11|2|A|태백산'

, '|'

, '†'

, 3

, @outErrorCode = @OutResult output

print @OutResult

select * from test_row_multi_insert;