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 or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to INSERT a string that contains single-quotes?

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':

UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'

Here's the error I get when I try this code in SQL Server:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.

Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:

string strUpdate = "UPDATE Comments SET Comment='";
strUpdate = strUpdate + FCKeditor1.Value;
//strUpdate = strUpdate + ThisUserCommentTextBox.Text;
strUpdate = strUpdate + "' WHERE UserID='";
strUpdate = strUpdate + (string)Session["UserID"];
strUpdate = strUpdate + "'";
strUpdate = strUpdate + " AND GameID='";
strUpdate = strUpdate + Request.QueryString["GameID"];
strUpdate = strUpdate + "'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

I'm using SQL Server 2005 and ASP.NET 2.0

Much thanks

ok i wont go through your code...but i can tell you the key point

try run

SELECT 'AAAA' SQL retuen AAAA

SELECT '''AAAA''' SQL return 'AAAA' (you need 3 * ' + AAAA + 3 * ' )

hope this give u idea

|||

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

|||

limno:

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

This is the best advice you will get today. If you value your applications you will never ever build an Sql statement that way again

string strUpdate = "UPDATE Comments SET Comment= @.Comment WHERE UserID = @.UserID AND GameID = @.GameID'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

myCommand.Parameters.AddWithValue( "@.Comment", FCKeditor1.Value );
myCommand.Parameters.AddWithValue( "@.UserID", Session["UserID"] );
myCommand.Parameters.AddWithValue( "@.GameID", Request.QueryString["GameID"]);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

|||

Thanks, it works now.

How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

how to include an apostrophy inside a string

I am doing a row update in a table and the text contains a ' character, just
wondering how to do this?
for example
UPDATE table
SET field2 = 'text to enter to table's but does not work'
WHERE field1 = 133
the second apostrophy I want to not end the text section with.
Thanks.
--
Paul G
Software engineer.YOu have to double quote that:
UPDATE table
SET field2 = 'text to enter to table''s but does not work'
WHERE field1 = 133
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> I am doing a row update in a table and the text contains a ' character, just
> wondering how to do this?
> for example
> UPDATE table
> SET field2 = 'text to enter to table's but does not work'
> WHERE field1 = 133
> the second apostrophy I want to not end the text section with.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks. One other question, when I use select from query analizer to
retreive a long text field and then copy and paste it to a word doc it looks
like it only returns the first portion of the large text string. Also is
there anyway to view a large text field from query analyzer when you open the
table? thanks again.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> YOu have to double quote that:
> UPDATE table
> SET field2 = 'text to enter to table''s but does not work'
> WHERE field1 = 133
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > I am doing a row update in a table and the text contains a ' character, just
> > wondering how to do this?
> >
> > for example
> > UPDATE table
> > SET field2 = 'text to enter to table's but does not work'
> > WHERE field1 = 133
> > the second apostrophy I want to not end the text section with.
> > Thanks.
> >
> > --
> > Paul G
> > Software engineer.|||QA is limited to a maximum outpur of 8000 characters, if you don´t have even
that you should look in Tools--> Options --> Results --> Maximum charcters
per column
to increase it to 8000
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> ok thanks. One other question, when I use select from query analizer to
> retreive a long text field and then copy and paste it to a word doc it looks
> like it only returns the first portion of the large text string. Also is
> there anyway to view a large text field from query analyzer when you open the
> table? thanks again.
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > YOu have to double quote that:
> >
> > UPDATE table
> > SET field2 = 'text to enter to table''s but does not work'
> > WHERE field1 = 133
> >
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > I am doing a row update in a table and the text contains a ' character, just
> > > wondering how to do this?
> > >
> > > for example
> > > UPDATE table
> > > SET field2 = 'text to enter to table's but does not work'
> > > WHERE field1 = 133
> > > the second apostrophy I want to not end the text section with.
> > > Thanks.
> > >
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks that should do it as it is way less than 8000 characters.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Just wondering if you know how to direct the results to a file?
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
Results in File
or you use OSQL on the commandline with the -o <outputfile >switch
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> Just wondering if you know how to direct the results to a file?
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > that you should look in Tools--> Options --> Results --> Maximum charcters
> > per column
> >
> > to increase it to 8000
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > ok thanks. One other question, when I use select from query analizer to
> > > retreive a long text field and then copy and paste it to a word doc it looks
> > > like it only returns the first portion of the large text string. Also is
> > > there anyway to view a large text field from query analyzer when you open the
> > > table? thanks again.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Jens Sü�meyer" wrote:
> > >
> > > > YOu have to double quote that:
> > > >
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table''s but does not work'
> > > > WHERE field1 = 133
> > > >
> > > >
> > > > --
> > > > HTH, Jens Suessmeyer.
> > > >
> > > > --
> > > > http://www.sqlserver2005.de
> > > > --
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > wondering how to do this?
> > > > >
> > > > > for example
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > WHERE field1 = 133
> > > > > the second apostrophy I want to not end the text section with.
> > > > > Thanks.
> > > > >
> > > > > --
> > > > > Paul G
> > > > > Software engineer.|||ok thanks this seems very useful.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
> Results in File
> or you use OSQL on the commandline with the -o <outputfile >switch
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > Just wondering if you know how to direct the results to a file?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > > that you should look in Tools--> Options --> Results --> Maximum charcters
> > > per column
> > >
> > > to increase it to 8000
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > ok thanks. One other question, when I use select from query analizer to
> > > > retreive a long text field and then copy and paste it to a word doc it looks
> > > > like it only returns the first portion of the large text string. Also is
> > > > there anyway to view a large text field from query analyzer when you open the
> > > > table? thanks again.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Jens Sü�meyer" wrote:
> > > >
> > > > > YOu have to double quote that:
> > > > >
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table''s but does not work'
> > > > > WHERE field1 = 133
> > > > >
> > > > >
> > > > > --
> > > > > HTH, Jens Suessmeyer.
> > > > >
> > > > > --
> > > > > http://www.sqlserver2005.de
> > > > > --
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > > wondering how to do this?
> > > > > >
> > > > > > for example
> > > > > > UPDATE table
> > > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > > WHERE field1 = 133
> > > > > > the second apostrophy I want to not end the text section with.
> > > > > > Thanks.
> > > > > >
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.sql

how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT (fields)

FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATE T2

SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime

FROM T2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

What's the schemas of the 3 tables T1,T2 and View1? I tried a test script using your statements, the query went fine and no deadlock occurred.

Q1. Do not put index on low selective column (as you said only 2 distinct values), because it dosen't make sense. Here are some guidelines for choosing columns to index from "Programming a Microsoft? SQL Server? 2000 Database":

Indexes are useful, but they consume disk space and incur overhead and maintenance costs. Consider the following facts and guidelines about indexes:

When you modify data on an indexed column, SQL Server updates the associated indexes.
|||

Thanks for your help, can you let me know how I can make this transaction low isolation level? I am quite new in the transaction isolation level.

|||

Simply use the SET TRANSACTION ISOLATION LEVEL command to set the session option, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT(fields)

FROMT2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATET2

SETTextField2 = 'Ok',TextField2Date=@.MyRunDateTime

FROMT2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

i think youre just maintaing the data on T2

based from the inserted values on T1.

how about just doing the insert on T1

and placing the update codes on

an "after insert trigger" in t1.

a transaction may not be necessary in this case

the previous design will cause a lot of deadlock in the system

|||

Jim,

You should consider defining indexes on fields listed in "where" clause.
If the problem persists after that, then more detailed examination will be needed.

Indeed, you can invoke asynchronous calls to SQL Server. You can use separate thread to handle the calls to SQL, or you can use built-in support for asynchronous SQL calls. For example, look at SqlCommand.BeginExecuteNonQuery Method at MSDN.

how to improve the Performance

Hi There
We've a audit_log table with 82 million records. We have trigger on each
table in the database to log any changes for update or insert or delete for
all fields.
we dont have any primary key on the table, just only one index
Will it affect any performance on server side or client side while inserting
records. Just we thought about truncate records. But we have to change some
applications if we truncate.
Thanks
Thanks
Ganesh
--
Thanks
GaneshWhat are wanting to improve the performance of: inserts, selects, etc.? You
only mention inserts, so I will focus on that. When a row is inserted,
additional I/O and CPU time is spent updating indexes, so keep indexes on
the inserted table to a minimum.
Actually, you not need any indexes on this table at all. You could implement
a partitioned table strategy. For example, the trigger could perform the
initial insert into a table called [audit_current]. You could then schedule
a job that once per hour / day / w etc. will insert rows from
[audit_current] into [audit_history] and then truncate [audit_current].
Therefore, [audit_current] will contain only a small number of rows and can
be inserted quickly. The table table [audit_history] would contain the
remaining history of > 82 million rows, and and can be indexed more heavily
for select queries. On those occasions where you need to query the audit
data, you can manually run the InsertAuditHistory job to insure that it
contains all rows up to that point in time.
To understand what type of I/O and CPU is required for inserting or
selecting the audit table, you can use the following:
SET STATISTICS IO ON
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
select * from audit where ...
update audit ...
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
SET STATISTICS PROFILE OFF
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:BC71293F-0BBB-4209-897F-C068FA02CE2E@.microsoft.com...
> Hi There
> We've a audit_log table with 82 million records. We have trigger on each
> table in the database to log any changes for update or insert or delete
> for
> all fields.
> we dont have any primary key on the table, just only one index
> Will it affect any performance on server side or client side while
> inserting
> records. Just we thought about truncate records. But we have to change
> some
> applications if we truncate.
> Thanks
> --
> Thanks
> Ganesh
> --
> Thanks
> Ganesh|||Thanks for you reply
I want to improve Insert, Currently the application is being used by around
500 user it's very slow now. I thought this audit_log also may be improve
performance if we truncate this one. and also takes spaces too.
Thanks
Ganesh
"JT" wrote:

> What are wanting to improve the performance of: inserts, selects, etc.? Yo
u
> only mention inserts, so I will focus on that. When a row is inserted,
> additional I/O and CPU time is spent updating indexes, so keep indexes on
> the inserted table to a minimum.
> Actually, you not need any indexes on this table at all. You could impleme
nt
> a partitioned table strategy. For example, the trigger could perform the
> initial insert into a table called [audit_current]. You could then schedul
e
> a job that once per hour / day / w etc. will insert rows from
> [audit_current] into [audit_history] and then truncate [audit_current].
> Therefore, [audit_current] will contain only a small number of rows and ca
n
> be inserted quickly. The table table [audit_history] would contain the
> remaining history of > 82 million rows, and and can be indexed more heavil
y
> for select queries. On those occasions where you need to query the audit
> data, you can manually run the InsertAuditHistory job to insure that it
> contains all rows up to that point in time.
> To understand what type of I/O and CPU is required for inserting or
> selecting the audit table, you can use the following:
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> SET STATISTICS PROFILE ON
> select * from audit where ...
> update audit ...
> SET STATISTICS IO OFF
> SET STATISTICS TIME OFF
> SET STATISTICS PROFILE OFF
>
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:BC71293F-0BBB-4209-897F-C068FA02CE2E@.microsoft.com...
>
>|||It sounds like your application makes frequent updates to tables, and you
are needing to audit all of this.
To benefit from parallel I/O processing, consider placing the audit tables
on a seperate and disk. Your log, audit_current, and audit_history tables
could perhaps be placed in the same file group.
http://msdn2.microsoft.com/en-us/library/ms187087.aspx
http://www.databasejournal.com/feat...cle.php/3114651
It is also possible that your insert performance is not related to I/O but
to blocking. When you have several processes attempting to insert into a
table simultaneously, there is a potential for blocking and even
deadlocking.
http://support.microsoft.com/defaul...kb;EN-US;224453
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:6FB90B35-7449-4FF6-91ED-984F25BCB9BA@.microsoft.com...
> Thanks for you reply
> I want to improve Insert, Currently the application is being used by
> around
> 500 user it's very slow now. I thought this audit_log also may be improve
> performance if we truncate this one. and also takes spaces too.
>
> --
> Thanks
> Ganesh
>
> "JT" wrote:
>

how to improve "update" speed

hi

in asp.net,i used sql server to store records.

in a table has 1 million records,but when i update the record,it is very slowly.

is "create index" helpful for "update" operation?

i need help,thanks a lot.

First ensure that you have properly created index on you table and primary key.

Second ensure that you have data in table only for one business period( on principle one year).

Make shrink on your data table.

Refactor your data index.

This will help you.

|||

Post the update statement you have as well as all the table information. Do an "EXEC sp_Help TableName" in your query analyzer and post the results here.

|||

Hi ndinakar:

mytable has 100,000 records, i use "exec sp_help mytable",its result is :

Name Owner Type Created_datetime

mytable dbo user table 2007-11-30 9:34:21

can i get further information

thanks a lot

|||

Hi yugiant,

Based on my understanding, you are now tring to creating indexes in your sql server database in order to improve your update performance. If I've understood you wrong, please feel free to tell me, thanks.

Yes I think using index is a good idea. Using indexes in your database will save a lot of time for locating the specific record. And since we need to first locate the record before updating it, I think using index is a great help for improving updating performance. exec sp_help will list properties of your table, including all the indexex you have made to your datatable. You can also get your datatable index information from Object Explorer(in the Indexes tree view node).

I strongly suggesting you reading this:http://msdn2.microsoft.com/en-us/library/ms189051.aspx I think it will be much of help to solve your problem. thanks.

Hope my suggestion helps

How to Import from flat file and update DateTime column?

I have a a flat file that consists of 2 Columns of data that need to overwrite an existing Table that has 3 Columns of data. The Import fails because the 3rd column on the table is a Date stamp column with the Data Type of "smalldatetime" and does not allow Null data. If I were to delete this 3rd column from the table the import works great but I lose the DateTime column. How can I use the Import Wizard to import the first 2 columns from a text file and update the 3rd column with the date and time? The wizard does not seem to let me update a column unless the data for this column comes from the flat file. Please assist, thanx.

Which wizard are you talking about? The import / export data wizard? If so this wizard is for appending new rows or deleting existing rows. I do not believe that it is really meant for updating rows (i.e. overwriting). You would want to create an SSIS package and use the OLE DB Command instead of the OLE DB Source to overwrite a row.

|||

If you are looking to import data, you can use the wizard to build the package, then save it and open it in Business Intelligence Developer Studio. If you then open the data flow, you can add a Derived Column task, add a new column to it, and use the GETDATE() function to return the current date.

|||If he did what you suggest wouldn't he still just be importing new records instead of overwriting the existing records?

|||

I believe the import/export wizard gives you the ability of providing a query for the import. Perhaps you can write a where you have a 3rd columns with the getdate(if using sql server) or sysdate(for Oracle) or any other DB function that retrieves the current date.

|||From his description, I wasn't positive that he wanted to "update" existing records as much as he was looking to truncate and reload the table. Using the wizard (and setting the create destination table options) will drop and recreate the table before loading it. However, I might have his needs wrong. Maybe he'll respond and let us know if either approach was successful.

|||

I'm sure the OP will realise that when the going gets tough, the tough-SSIS-community-contributors get going.

Sorry.

How to impletement this query in SSIS package

Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?

update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1

Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps).|||

Hi Phil,

if I can't not write SQL script to impletement this, I have to use condition split (for column01<>-1 ) -> Sort->Merge Join(Join dbo.columnlist table )->Condition Split (column from dbo.columnlist is null), derived column (update column01 with -1) -> Union All.

it looks so complicated.

do you know is there any component that we can write script to update the input column set? I know we can use VBScript component to do this , but I'm wondering if we can use SQL Script.

|||I think you should perform a lookup transformation against "select column from dbo.columnlist".

Using the lookup error output, you can add a derived column that sets column01 to -1. Then, use a union all to join the normal lookup output with its error output.

Don't worry about the "and column01 <> -1" statement. It is worthless.|||

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

I tried with 'enable memeory restriction' and modify the SQL Statement , replacing the '=' with '<>', but it prompts error when I debug, saying 'no match rows', but actually I do have match rows.

|||

Jeff_LIU wrote:

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow).

HOW TO IMPLEMENT UPDATE INMEDIATE WITH ONE SUBSCRIBER AND MULTIPLE PUBLISHER

Hi, i have SQL2k SP4.
My replication topology is:
1 Subscriber central and 4 publisher (no conflic)
ok, the subscriber is necesary update inmediate to publicher data, for
example:
Pub A containt
Empresa_id # Sucursal_id # monto
-- -- --
1 1 100
2 1 200
Pub B containt
Empresa_id # Sucursal_id # monto
-- -- --
1 2 100
2 2 200
Subscriber containt
Empresa_id # Sucursal_id # monto
-- -- --
1 1 100
2 1 200
1 2 100
2 2 200
The PK is Empresa_id + Sucursal_id
ok.
I need the suscriber update inmediate to publicher, for example:
UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=2
The update is necesary distributed transaction to PUB B only.
UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=1
The update is necesary distributed transaction to PUB A only.
------
ok, whats is major method to implement the scenario?
Tks

Salu2
Microsoft MVP SQL Server
Culminis Speaker
make your central subscriber the publisher and use immediate updating on the
subscribers. Then make sure your polling interval is low. You should get the
majority of your inserts happening in seconds.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maxi" <maxi_da@.infovia.com.ar.sacame> wrote in message
news:u8qGG1BMHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hi, i have SQL2k SP4.
> My replication topology is:
> 1 Subscriber central and 4 publisher (no conflic)
> ok, the subscriber is necesary update inmediate to publicher data, for
> example:
> Pub A containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 1 100
> 2 1 200
> Pub B containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 2 100
> 2 2 200
>
> Subscriber containt
> Empresa_id # Sucursal_id # monto
> -- -- --
> 1 1 100
> 2 1 200
> 1 2 100
> 2 2 200
>
> The PK is Empresa_id + Sucursal_id
> ok.
> I need the suscriber update inmediate to publicher, for example:
> UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=2
> The update is necesary distributed transaction to PUB B only.
> UPDATE TABLE SET MONTO=300 WHERE EMPRESA=2 AND SUCURSAL=1
> The update is necesary distributed transaction to PUB A only.
> ------
> ok, whats is major method to implement the scenario?
> Tks
>
> --
> Salu2
> Microsoft MVP SQL Server
> Culminis Speaker
>
|||Hi, my central subscriber is make inmmediate update!
But it has but publisher (4) and it is needed that subscriber it updates
when suitable publisher with MSDTC. Single trigger updates one single one
and I need that depending on the filter she updates to publicher X
Salu2
Microsoft MVP SQL Server
Culminis Speaker
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:uMtXDBNMHHA.448@.TK2MSFTNGP04.phx.gbl...
> make your central subscriber the publisher and use immediate updating on
> the subscribers. Then make sure your polling interval is low. You should
> get the majority of your inserts happening in seconds.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Maxi" <maxi_da@.infovia.com.ar.sacame> wrote in message
> news:u8qGG1BMHHA.1240@.TK2MSFTNGP03.phx.gbl...
>