.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Having trouble updating 1,000,000 row table

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

Using this T-SQL statement this process should take about 2 minutes.  We know this by limiting the update to 100,000 rows. (the table has an identity field so we can control how many and where to start).  100,000 rows update in 11 sec.  That translates to about 2 minutes for the entire million row table.  There is some kind of problem that resolves itself after a while.  We timed the statement in a larger proc where it was contained and it eventually finished after 2 to 4 hours. 






[Cost] = dw_Prod.

View Complete Post

More Related Resource Links

Updating table from a table on another server

I am updating a column in table from a table on another server and am having some troubles. My code looks like this...   UPDATE TABLE1 SET COLUMN1A = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2A FROM TABLE1, SERVER2.DATABASE2.SCHEMA2.TABLE2 WHERE TABLE1.COLUMN1B = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2B   I keep getting "The multi-part identifier ____ could not be bound."   Security rights are in place and I have access to Server2    Thoughts?

trouble getting dates right when importing Access table

I was given a bunch of data in Access and am putting it in SQL 2005 for an ASP.NET web app. Tryingto do a query to select only records where the current date is after the "postDate" field and on or before the "expireDate" field. WHERE (postDate <= CURRENT_TIMESTAMP) AND (expireDate >= CURRENT_TIMESTAMP) I'm getting no records returned even though there are some records that qualify. Tried using CURDATE() and NOW(), no difference. At this point I think it may be because the dates are not in the correct format. The columns were originally "Date/Time" format in Access and when I imported them into SQL using the DTSWizard, the datatype for those columns became "smalldatetime." Dates are in the format M/DD/YYYY HH:MM:SS. I'm guessing the query won't work because SQL is expecting YYYY-MM-DD. How do I fix this? Been spending a depressing amount of time running in circles with this.

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


convert string to int before updating database table?


I have a form in which I display data from a database table, change the data and then update the database table. The form consists of textboxes, so all data displayed in the form is string. However - one data is int in the database table (the rest is varchar), so I need to convert it from string to int when I update the database table. How do I do it?

protected void SaveProd_Click(object sender, EventArgs e)
        string BildID = TextBoxProdPictID.Text;
        string namn = TextBoxProdName.Text;
        string kategori = TextBoxProdCategory.Text;
        string stl = TextBoxProdSize.Text;
        string pris = TextBoxProdPrice.Text;
        string iLager = TextBoxProdNumberOf.Text; //this is int in the database table

        SqlConnection conn = new SqlConnection(config);
      //  conn.Open();
        string sql = "UPDATE Produkter SET kategori ='" + kategori + "', namn ='" + namn + "', stl ='" + stl + "', pris ='" + pris + "', iLager ='" + iLager + "' WHERE ID ='" + ID + "'";
        SqlCommand comm = new SqlCommand(sql, conn);//Conversion failed when converting the varchar value '__Page' to data type int.
        SqlDataReader dr = comm.ExecuteReader(); 

        //clear t

Trouble Using More Than One LIKE Operator in a Table Filter in SSRS 2008


Hello All,

I'm having what I'm hoping will be a simple issue -

My co-worker has created a large report which contains several tables in SSRS 2008.  Each table is tied to one dataset, yet only certain data can be returned to each respective table.  Right now he's applying filters at the table-level behind each table, however as you can imagine this is not efficient because there is a LOT of data returning for this report. 

I'll try and give a simplified example -

There are two tables in the report body - one for containing products relating to both Cats and Dogs, and one for containing products relating to Birds.   Each product starts with the name of the specific animal - for example "Cats-Mouse Toy", "Dogs-Frisbee", "Birds-Rubber_Leaf".  

Now I need to somehow filter out each product so that they go to their proper tables.  As mentioned above, my co-worker was creating a specific filter for each product, but this is proving to be inefficient.  For example, there are 52 different products for Birds alone.  That would mean 52 different filter entries for each product.  What I would like to do is use the LIKE operator but i'm not getting the results I want on the first table.

So, on the first table I need something like:  =(Fields!Products.value LIKE &

Updating a table using a variable object


I have a variable of Data Type 'Object'. I populate this with 1 row of data via an Execute SQL Task control,  because I have a For Each Loop Container around this Execute SQL task control that gets 1 ID at a time. What I want to do is now that I have the row of data in my variable object, is update my table with the new data.

I am thinking I need to start with a Data Flow Task? But I'm not sure how/what to use to reference my variable so that I can map it to my destination.

Updating values of one column of table 1 from a different table


Hi I need to update one column with the values from a column from a different table joined by the subtypes. Please see below...







PRODUCT =(dbo.[Subtype-desc$].[Subtype-description])

Updating the design of an SQL table


guys i would like to update the design of a table.

Trying to save the design i am getting an error that the operation isn't permitted.

A constraint exist between this table and another table

any idea ? thanks


Dropdownlist is not updating the table

I am wondering what I need to correct , Detailsview is in ReadOnly mode. 
  <asp:BoundField DataField="ShipTo" HeaderText="ShipTo" SortExpression="ShipTo" />
                <asp:TemplateField HeaderText="PaymentMethod" SortExpression="PaymentMethod">
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("PaymentMethod") %>'></asp:TextBox>
                        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="AccessDatasource1"
                            DataTextField="PaymentType" DataValueField="id">
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("PaymentMethod") %>'></asp:TextBox>
                        <asp:Label ID="Label1" runat="

Multiuser SSIS Package Updating One table


Suppose I have oen SSIS package that update to ONE table and multiple users are executing that package (using Agent JOB). SO is there any chance that the package will fail for some user, due to table lock?

Note: For my OLEDB Source component I am using SQL query with a where condition for that user and that insert to OLEDB Target SQL Table.

If Yes, How should I handel multiuser situation here so that all users can insert into the same table.


Updating multiple Indexes on a table - Performance issue



I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated


Updating sql express 2008 database table from an XML file.


I'll start off with the usual caveats.. new to the forum, new to programming, not a programmer by trade..just a dabbler


A little background..


I run a hockey pool for me and a few friends. I currently use Excel to produce everything. Every week I download a csv file from a stats reseller, update my pool, then run some macros to create web pages and upload those to my site. It works alright but I want to take my pool/site to the next level. So I decided to do a new site using ASP.NET and sql express.


My plan is to download an XML file from the same reseller and update my "Player" table every week with the "new" stats.


I used a Dataset, SqlDataAdapter, and the XML file from the reseller to populate my "Player" table in the beginning. It was pretty slick, pretty easy. My problem is I can't seem to figure out how to update my "Player" table with my new stats.


First I tried using the same Dataset/SqlAdapter but it threw a Primary Key exception, saying I couldn't insert that ID because it was already existing in the table. I can fill my dataset from the "Player" table change things and save it back to the "Player" table..but that's not what I want to do.


In my min

I am updating a currency field of an access table through detailsview, cannot input decimals.


I changed a text field to a template field assigning the selected text and the selected value is being assigned to another currency field also templated. I get aa error

[FormatException: Input string was not in a correct format.]

Could you please give me a few tips on this one. Thanks

updating multiple rows for multiple columns of the table from other table



I have source table called customer. And data from this table is transfered to another database  with same table name.

that is database1(customer table) ----> database 2(customer table)

now in database 2, First time my program runs and it randomly get any name and ssn and then updates the first name, last name, ssn of all the customer_ID's in the customer table (I use RAND function)

Now second when i run the program, it recreates the name and ssn again but different, for the same customer_id.

So i want that once the particular customer_ID is updated, next time if i run the program. it shold not update the those updated customer id's

Thanks, Any help is appreciated.


LTRIM to a VIEW without updating the table


Hi All,

I have a VIEW that compares two fields from two tables in the same database, the trouble is that one field has leading zero's and the other doesnt, I cannot update the table containing the field with leading zero's as this will impact on other process that have been wriiten to compensate for the leading zero's and would therefore like to write the update into the VIEW, so that the table data is not disturbed.

This is an excerpt from the code...


WHEN ledgertrans.paymreference!='-%' 

THEN bankchequetable.AccountID 


WHEN ledgertrans.paymreference='-%' AND ledgertrans.paymreference=vendtrans.voucher AND vendtrans.paymreference=bankchequetable.chequenum 

THEN bankchequetable.AccountID 



Updating a table - or add when the record doesn't exist



I'd like to be able to update a table from a VB application without the table being part of the project.

I know how to insert a record:


Dim connectionstring As String = My.Settings.AdminCentreConnectionString

Dim eSQL As String =

Accessing Oracle Table data and updating using SharePoint 2010 List



We are trying to access Oracle database table through SharePoint 2010 List. The functionality required is, the user should be able to read the data from the Oracle table in to the SharePoint 2010 List and should also be allowed to update the list and in turn the data should be updated back in to the oracle table.

Over and above any changes made to the oracle table by users, should be updated in the sharepoint 2010 list. Which means the users require a two synchronisation from SharePoint 2010 List to Oracle table and vice versa.

let us know if anyone has worked on this before.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend