Iteration through many tables to perform update

...Ok so I have one field that exists in an many ARCHIVE tables(approx 25).
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>

It may need to be a trigger

Hi,

I needed to perform an update of a database table row when another table gets an insert.

Would I use a trigger to do this?, If so, how?

I will need to pass two of the feilds just inserted to perform the update on the second table.

Yes, you can use a trigger to do that.

Issues with an output param from a sproc using SQLDataSource

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.

The proc looks like this currently:

ALTERproc [dbo].[k_sp_Load_IMIS_to_POP_x]

@.vcOutputMsgvarchar(255)OUTPUT

AS

SETNOCOUNTON;

select @.vcOutputMsg='asdf'

The code behind looks like this:

protectedvoid SqlDataSource1_Updated(object sender,SqlDataSourceStatusEventArgs e)

{

//handle error on return

string returnmessage = (string)e.Command.Parameters["@.vcOutputMsg"].Value;

}

On the page source side, the params are defined declaratively:

<UpdateParameters>

<asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"/>

<asp:ParameterDirection="InputOutput"Name="vcOutputMsg"Type="String"/>

</UpdateParameters>

When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"

PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

I got mine to work. Here's my example.

ASPX

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="false" autogenerateeditbutton="true"datasourceid="SqlDataSource1"><columns><asp:boundfield datafield="ShipperID" headertext="ShipperID" readonly="True" /><asp:boundfield datafield="CompanyName" headertext="CompanyName" readonly="True" /><asp:boundfield datafield="Phone" headertext="Phone" readonly="True" /></columns></asp:gridview><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onupdated="SqlDataSource1_Updated" selectcommand="SELECT * FROM [Shippers]" updatecommand="sp_GetMessage"updatecommandtype="StoredProcedure"><updateparameters><asp:parameter direction="InputOutput" name="Message" size="50" type="String" /></updateparameters></asp:sqldatasource><asp:label id="Label1" runat="server" />

CODE-BEHIND

protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e){Label1.Text = e.Command.Parameters["@.Message"].Value.ToString();}

STORED PROCEDURE

ALTER PROCEDURE dbo.sp_GetMessage(@.MessageAS VARCHAR(50)OUTPUT)ASBEGINSELECT @.Message ='Hello World!'END

|||

Mine looked identical to yours except for the size on the output param. Once I added it in, it worked beautifully!! Not sure why it wasn't automatically declared when I configged the SQLDatasource to use the update method. But hey, it works now!!!

Thanks for the help!!!!!

Bill

|||

Mine didn't get created either, except I got another error actually referencing the absence of a size. I'm not too sure why you didn't receive the same. I also don't know why the Size attribute is needed.

issue with update

Hello,

I have the following PERFORMANCE issue:

I have created a job that fill and update a database from a source db with same structure.

The problem is that at the beginning performance was good, now the source db and destination db are very large and time to import/update is to big.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk.

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

How can I increase performance and reduce time of importing?

Thank

You can try the replication service instead of doing it manually, it will be faster than the DTS job.

If you want to it manually do the following operations,

1. Delete

2. Update

3. Insert

|||

Hello,

thank, but at the beginning we tried with replication but there are some problems, so we decides to use other methods such as the above one.

Now, I am thinking to create a trigger on each big tables to identify what record is inserted new or updated. The trigger populate a table that represent only these pk. In this way I can make a subset of record that are only new or updated.

Issue with getting values from child controls in a gridview, to use for the update using a

Hi all,

I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:

<

asp:GridViewID="GridEditSettlement"runat="server"AutoGenerateColumns="False"BackColor="Navy"BorderColor="IndianRed"BorderStyle="Solid"Font-Names="Verdana"Font-Size="X-Small"DataSourceID="SqlDataSource_grid"AllowPaging="True"AllowSorting="True"ForeColor="White"DataKeyNames="legid"><Columns><asp:CommandFieldShowEditButton="True"CancelImageUrl="~/App_Graphics/quit.gif"CancelText=""EditImageUrl="~/App_Graphics/EditGrid.GIF"EditText=""UpdateImageUrl="~/App_Graphics/save.gif"UpdateText=""ButtonType="Image"/><asp:BoundFieldDataField="StartDate"HeaderText="Start Date"ReadOnly="True"/><asp:BoundFieldDataField="EndDate"HeaderText="End Date"ReadOnly="True"/><asp:BoundFieldDataField="CounterpartDealRef"HeaderText="CP Deal Ref"ReadOnly="True"/>

<asp:TemplateFieldHeaderText="Preliminary Settlement Price"><ItemTemplate><asp:LabelID=lblPreliminaryrunat=serverText='<%# Bind("PrimarySettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtPrimaryPriceText='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:TemplateFieldHeaderText="Agreed Settlement Price"><ItemTemplate><asp:LabelID=lblAgreedrunat=serverText='<%# Bind("AgreedSettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtAgreedPriceText='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:BoundFieldDataField="Volume"HeaderText="Volume"ReadOnly="True"/><asp:BoundFieldDataField="Price"HeaderText="Price"ReadOnly="True"/><asp:BoundFieldDataField="TotalVolume"HeaderText="Total Volume"ReadOnly="True"/><asp:BoundFieldDataField="InstrumentName"HeaderText="Instrument"ReadOnly="True"/><asp:BoundFieldDataField="NominalValue"HeaderText="Nominal Value"ReadOnly="True"/><asp:BoundFieldDataField="Strike"HeaderText="Strike"ReadOnly="True"/><asp:BoundFieldDataField="DeliveryDate"HeaderText="Delivery Date"ReadOnly="True"/><asp:TemplateFieldHeaderText="LegId"SortExpression="LegId"><ItemTemplate><asp:LabelID="lblLegID"runat="server"Text='<%# Bind("LegId") %>'></asp:Label></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtLegIDText='<%# Bind("LegId") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

</Columns><RowStyleBackColor="#FFFF66"ForeColor="#333333"/><EditRowStyleBackColor="#FFFF66"Font-Names="Verdana"Font-Size="X-Small"ForeColor="#333333"/><PagerStyleForeColor="White"/><AlternatingRowStyleBackColor="White"ForeColor="#333333"/></asp:GridView> <br/>

<asp:SqlDataSourceID="SqlDataSource_grid"runat="server"ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>"SelectCommand="sp_get_single_deal"SelectCommandType="StoredProcedure"UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@.primarysettlement, AgreedSettlementprice=@.agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@.legid"EnableCaching="True"ConflictDetection="CompareAllValues"ProviderName="System.Data.SqlClient"><SelectParameters><asp:QueryStringParameterDefaultValue=""Name="dealnum"QueryStringField="deal"Type="String"/></SelectParameters><UpdateParameters><asp:ControlParameterControlID="txtLegId"PropertyName="Text"Name="legId"/><asp:ControlParameterControlID="txtPrimarySettlement"Name="primarysettlement"PropertyName="Text"/><asp:ControlParameterControlID="txtAgreedSettlement"Name="agreedsettlement"PropertyName="Text"/><asp:SessionParameterDefaultValue=""Name="userid"SessionField="userid"/></UpdateParameters></asp:SqlDataSource>

As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).

So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.

What is the best approach? Anyone come up against this issue before?

Thanks,

KB

You could try this way:

code in gridview: <asp:TemplateField HeaderText="company name" SortExpression="camcompany"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("camcompany")%>'></asp:Label> </ItemTemplate> </asp:TemplateField>code in sqlDataSource: <UpdateParameters> <asp:Parameter Name="camcompany" Type="String" /> ......... </UpdateParameters>

You can specify a row and access the controls within that row:

Dim lastNameAsString = selectRow.Cells(1).Text

You can access the individual cells of theGridViewRow object by using theCells property. If a cell contains other controls, you can retrieve a control from the cell by using theControls collection of the cell. You can also use theFindControl method of the cell to find the control, if the control has anID specified.

seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridviewrow.aspx for details.

Hope it helps.

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank