how to insert records from tiggers at batch update process

I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.

Pls help me........I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"

Post your trigger and maybe we can offer some suggestions.|||Hi

This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2

The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
but not for batch update.

if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.

pls help me.

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS
Declare @.Id int
Declare @.Res_id nvarchar(8)
Declare @.DTime smalldatetime
Declare @.StartTime char(5)
Declare @.EndTime char(5)
Declare @.Pauze char(5)
Declare @.Hourworked nvarchar(5)
Declare @.Status char(1)
--Declare @.PPayroll bit
Declare @.Project nvarchar(10)
Declare @.Naam char(50)
Declare @.Type char(5)
Declare @.Hryear nvarchar(4)
Declare @.Totmin char(5)
Declare @.Endtimebr char(5)
Declare @.Stime char(5)
Declare @.Start char(5)
Declare @.Processed char(1)
Declare @.del_DTime smalldatetime
Declare @.del_StartTime char(5)
Declare @.del_EndTime char(5)
Declare @.Del_id int
Declare @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

Select @.Id=Id,
@.Res_id=Res_id,
@.DTime=[Datetime],
@.Start=Starttime,
@.EndTime=Endtime,
@.Pauze=Pauze,
@.Hourworked=Hoursworked,
@.Status=Status,
@.Project=Project,
@.Naam=Naam,
@.Type=Type,
@.Hryear=Hryear,
@.Processed=Processed
From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]

Select @.del_DTime=[DateTime],
@.del_Starttime=Starttime,
@.del_endTime=Endtime ,
@.del_id=id
From Deleted

Delete From Selnmuamid
Where Res_id=@.Res_id
and Datetime=@.del_DTime
and Starttime >=@.del_starttime
and Endtime <=@.del_endtime

-- This is To Get total hours (hourworked column in the table ) in minutes

Select @.Totmin=Sum(Convert(Integer,(Left(@.hourworked,Char index('.',@.hourworked)-1) *
60)) +Convert(Integer,(Right(@.hourworked,Len(@.hourworke d) -
Charindex('.',@.hourworked))))),@.Endtimebr=Sum(Conv ert(Integer,(Left(@.Endtime,Charindex(':',@.Endtime) -1) *
60)) + Convert(Integer,(Right(@.Endtime,Len(@.Endtime) -
Charindex(':',@.Endtime))))),@.Stime=Sum(Convert(Int eger,(Left(@.Start,Charindex(':',@.Start)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))

--Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,
@.Hourworked,@.Status,@.Project,@.Naam,@.Type,
@.Hryear,@.Totmin,@.Endtimebr,@.STime,@.Processed|||basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.

This has NOT been tested!

Code:
------------------------------

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS

-- ----------------
-- One Declare is more efficient than multipule declares
-- ----------------
Declare @.Id int
, @.Res_id nvarchar(8)
, @.DTime smalldatetime
, @.StartTime char(5)
, @.EndTime char(5)
, @.Pauze char(5)
, @.Hourworked nvarchar(5)
, @.Status char(1)
-- , @.PPayroll bit
, @.Project nvarchar(10)
, @.Naam char(50)
, @.Type char(5)
, @.Hryear nvarchar(4)
, @.Totmin char(5)
, @.Endtimebr char(5)
, @.Stime char(5)
, @.Start char(5)
, @.Processed char(1)
, @.del_DTime smalldatetime
, @.del_StartTime char(5)
, @.del_EndTime char(5)
, @.Del_id int
, @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

select @.Id = min(ID) from inserted
while (@.Id is not null) begin
Select @.Res_id = Res_id
, @.DTime = [Datetime]
, @.Start = Starttime
, @.EndTime = Endtime
, @.Pauze = Pauze
, @.Hourworked = Hoursworked
, @.Status = Status
, @.Project = Project
, @.Naam = Naam
, @.Type = Type
, @.Hryear = Hryear
, @.Processed = Processed
-- ---------------------------------------------------
-- Hoursworked, Endtime and StartTime need to be converted to minutes
-- ---------------------------------------------------
, @.Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
, @.Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
, @.Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
From inserted i
where id = @.id


-- This is To Get total hours (hourworked column in the table ) in minutes
/*
Select @.Totmin = Sum(Convert(Integer,(Left(@.hourworked,Charindex('. ',@.hourworked)-1) * 60)) +
Convert(Integer,(Right(@.hourworked,Len(@.hourworked ) - Charindex('. ',@.hourworked)))))
, @.Endtimebr = Sum(Convert(Integer,(Left(@.Endtime,Charindex(':',@. Endtime)-1) * 60)) +
Convert(Integer,(Right(@.Endtime,Len(@.Endtime) - Charindex(':',@.Endtime)))))
, @.Stime = Sum(Convert(Integer,(Left(@.Start,Charindex(':',@.St art)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
*/
-- Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,@.Hourwor ked,@.Status,@.Project,@.Naam,@.Type,@.Hryear,@.Totmin,@. Endtimebr,@.STime,@.Processed

select @.Id = min(ID) from inserted where id > @.id
end

-- -------------------
-- The deleted temp table can be joined to the Selnmuamid table
-- to deleted the unwanted records. If you need the ID from the
-- inserted table then join the inserted table with the
-- deleted table and then join the Selnmuamid table.
-- This is a good example of how you can ditch the row-at-a-time
-- mentality and start using set theory for processing records.
-- -------------------
delete
from Selnmuamid s
join deleted d on s.Res_id = d.Res_id
where [s.Datetime] = d.del_DTime
and s.Starttime >= d.del_starttime
and s.Endtime <= d.del_endtime

/*
Select @.del_DTime = [DateTime]
, @.del_Starttime = Starttime
, @.del_endTime = Endtime
, @.del_id = [id]
From Deleted
where Id = @.Id

Delete
From Selnmuamid
Where Res_id = @.Res_id
and [Datetime] = @.del_DTime
and Starttime >= @.del_starttime
and Endtime <= @.del_endtime
*/

------------------------------

how to insert multiple records into table

insert into table1 (colname) values (value1)

can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?

We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take

value1, value2, value3 ...

from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

What is the source of the data? from another table?

|||

If you are taking values from another table then you can insert multiple records.

Any ways i think it internally dosen't make much difference if you use multiple records or single record multiply internall it will fire that many insert statements only.

I find this intresting article guess it will help

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

|||

U want place into the same table r other clarity is required

Insert into x select * from x

insert into x select * from y

If u want to copy the data of entire table use * other wise U have to specify column clause

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I hope I'm getting your question right.

Are you looking for something like

insert into table1 ( colname )
select value1 union all
select value2 union all
.
.
.
select valueN

If you can specify your problem in details then we can help you better.

|||

Thank everyone for your input. What I want was taking the contents of multiple records delimited by delimiter from clipboard as a string, then insert into a table. I complished by writing a user function which takes a string, then output a table with multiple records, it works as

insert into table1 (ID)

select values from fn_StringToTable(@.myString, @.delimiter) -- here @.myString='00a1,00a2,00a3,...'; @.delimiter=','

output table1 got the IDs from the clipboard which has'00a1,00a2,00a3,...

Thanks

How to increment record id in an insert stored procedure

Hello everyone,
I have a problem. I need to insert records from table2 to table1. In
table1 there is a record id which serves as the key of the table. Each time
a record needs to be inserted, a record id is created. This record id is th
e
max(recordid)+1. I wrote the following stored procedure to insert records.
This stored procedure will insert the ten records from table2 into table1.
The only problem is that the recordcount is same for all ten records, instea
d
of incrementing. Should I store the table id in a different table? Any
response will be greatly appreciated.
Begin Transaction
(Select @.recordcount = (max(record)+1) from [dbo].[table1])
Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
field5, field6)
Select @.recordcount, field1, field2, field3, field4, field5, field6 from
[dbo].[table2]
select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
if @.errnum <> 0 GOTO sqlerror
Commit TransactionLook up IDENTITY in Books Online, that does all the work for you.
Jacco Schalkwijk
SQL Server MVP
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:765A9768-316A-4446-82A6-58737F5040CB@.microsoft.com...
> Hello everyone,
> I have a problem. I need to insert records from table2 to table1. In
> table1 there is a record id which serves as the key of the table. Each
> time
> a record needs to be inserted, a record id is created. This record id is
> the
> max(recordid)+1. I wrote the following stored procedure to insert records.
> This stored procedure will insert the ten records from table2 into table1.
> The only problem is that the recordcount is same for all ten records,
> instead
> of incrementing. Should I store the table id in a different table? Any
> response will be greatly appreciated.
> Begin Transaction
> (Select @.recordcount = (max(record)+1) from [dbo].[table1])
> Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
> field5, field6)
> Select @.recordcount, field1, field2, field3, field4, field5, field6 from
> [dbo].[table2]
> select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
> if @.errnum <> 0 GOTO sqlerror
> Commit Transaction
>