okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?
here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)
update t1 set books_music='' from mailorder t1 where books_music is null
update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null
update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null
update t1 set Computer='' from mailorder t1 where Computer is null
update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null
in FoxSlow (foxpro) i could just do something like this:
do while !eof()
replace books_music with '' for books_music=null
replace car_buff with '' for car_buff=null
skip
enddo
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).
The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.
I have a number of questions:
How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?
Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.
What is being passed? Can you pass an array or something to a stored procedure?
As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.
UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer] WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));
I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?
Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes': 1) "Update tblDept SET IsTop ='No' 2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 " Cheers!
Hi, I just started learning ASP.NET this week and have watched a mountain of videos from this website which has helped me alot However I have been stuck on a problem for 2 days now. I have created an SQL database with the Following 2 tables: USERS COMPUTERSUserid Computerid firstname Useridsecondname Manufacturer Model I have made a relationship between the 2 tables. I then created a dataset with the following query:SELECT COMPUTERS.Computer_ID, COMPUTERS.Manufacturer, COMPUTERS.Model, USERS.First_Name, USERS.Last_Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID I however do not get the option in my grid view when i output this data, to UPDATE. The best i have found from google is that i need to use subqueries and not innerjoins but i just cant seem to get my head around them, please help as i feel my head may just explode if i think about this or try any more ways to get this to work :D
I am using MSDE Release A was wondering what is the best way to find out when there are critical updates for MSDE. Is there a notification service for MSDE like there is for Windows?
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty WHERE item_no=@item_no
UPDATE Items SET Sales=@Total_Sales, Qty=@Total_Qty WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
Hi I am using SQL Server 6.5 as my backend for a VB6 Application. This is a OLTP kind of package that I have developed with about 600 Users. I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application. My database has grown considerably and all of a sudden, all the CommitTran failed. None of the records were saved.
When I stopped and restarted my SQL Server, the operation became normal all Update/Insert queries for the same Application succeeded. (Note that I have not stopped my Server for the last 45 days).
WHY DOES THIS HAPPEN? HOW DO I AVOID THIS IN FUTURE... PLEASE HELP Thanks Jivee
How would I check a db to see if a record exists and if it does then do an update but if it doesn't do an insert. so: Set oRS = objDB.Execute("select loginid from computers where loginid='" & WshNetwork.Computername & WshNetwork.UserName & "'")
If that returns something then do an update but if not then do the insert.
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions
well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(
Hello, I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Hello guys, I am askng for any help ...am trying to get this SQL language.
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. -------------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
i want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. Thank you in advance. Shcema---------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
Hi all, working as an intern at MS for a year (SDET) and I've been asked to find a way of transfering items from one sharepoint lists to another (intranet to extranet) without access to the underlying SQL servers'. So what I've done is use an access databaes as a bridge creating active views of the two sharepoint lists. I wrote a C# console application that executed some SQL on the bridge to copy data between the two sharepoint lists, this was great could handle updates, deletions and additions of new items. Now I've been asked to make this update process 2 way on a certain field (status) I've been trying for a couple of days now and I'm having no joy. The method I was using (polling) allowed for the first couple of status changes (used 2 statements) but then wrote back the wrong way see below for example SQL:
Extranet owned statuses: Declined, Approved, Not Started, In-Progress, Completed. Intranet owned statuses: Needs Review, MS Declined
UPDATE intranet JOIN extranet ON (blahblah=blahblah)SEt intranet.status=extranet.status WHERE extranet.status IN (all extranet owned statuses);
UPDATE extranet JOIN intranet ON (blahblah=blahblah)SEt extranet.status=intranet.status WHERE intranet.status IN (all intranet owned statuses);
Can anybody tell me where I'm going wrong and offer any suggestions?
I have 2 databases with the same tables, etc. I'm trying to copy one column from the same table from db1 to db2. Here is my sql:
update db1.dbo.table1 set db1.dbo.table1.memtype = db2.dbo.table1.memtype where db1.dbo.table1.memid = db2.dbo.table1.memid
When I parse it, it completes successfully but when I execute it, I get this error message: The multi-part identifier "db2.dbo.table1.memid" could not be bound.
Can someone help me with the following? I need to update all columns in a table where the value is zero with NULL where the row id's (primary keys) are between two values. I was hoping that I could do this without "hard coding" the column names as there are many columns. i.e:
UPDATE TABLENAME FOR ALL COLUMNS SET VALUE = NULL WHERE VALUE = 0 FOR ROWS BETWEEN n and m
Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??
We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.
I have a pocket pc app that is using a sql ce (.sdf) database. The inserts, updates and deletes are not persisting when I exit debug. It seems like it's getting put in the database because I bind drop down lists after the inserts, updates, etc and the new/changed data shows correctly after rebinding the data (when I repopulate the drop downs I make a call to the database, I don't just manually add the new item to the drop down so it seems like the changes are getting to the database). However, once I stop debugging and restart debugging all my changes are gone. I'm not using transactions but is there something that I need to do to commit the db changes?
Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?
Here's what I'm currently doing: I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly: - sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records); - performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated); - updating the table when each group has been processed.
This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.
Any ideas on how I can improve performance of my updating?
I updated my default (only) instance of sql2k to 2005. I'm now working on fixing backward compatibility issues, among these sp_configure 'allow updates'.
According to the BOL, reconfigure will throw an error if 'allow updates' is set to 1. And updates to system tables is disallowed entirely. However, when I started my work on a script that does these things, it ran without error on the newly updated server.
This code works, and if I understand the BOL correctly, it should not: sp_configure "Allow Updates", 1 GO reconfigure with override <-- works without complaint GO update sysusers set name='283c' where uid=31 <-- works without complaint update sysusers set name='283c' where uid=31 <-- works without complaint go sp_configure "Allow Updates", 0 GO reconfigure GO
Did I miss something in my upgrade? I used the wizard. The management tool reports my version as 8.0.760. This database was present during the upgrade. I have not restored it from backup.
how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed
I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
Create Procedure UpdateGameIsOverlappingFlagByIds (@gameDateIds as varchar(200) = '') as begin if (@gameDateIds = '') raiserror('UpdateGameIsOverlappingFlagByIds: Missing parameters', 16,1) else begin Execute ('update games Set IsOverlapping = 1 where gameDateId IN (' + @gameDateIds + ')')
end end return -------------------------------------------- That is the sproc that doesn't seem to update the date when called from asp.net but does work when done through the query analyzer. Am I missing something?
I'm having problems with my translog backups failing when there has been a nonlogged entry, how can I keep this from happening.....and what exactly is a nonlogged entry?
