First off, Ive been asking a lot of questions lately and I want to sincerely thank those who have been posting VERY helpful replies. I have learned much in these past few weeks.
That being said I have hopefully one of my last questions before this project is complete.
Im running a DTS that imports data from a flat text file and updates 6 different tables. In one of my tables, "PERSON", I only want the records to be inserted or updated if the FCN field is new, or unique.
Here is my code that updates the table (without regard for uniquneess):
INSERT INTO PERSON (FIRST_NAME, LAST_NAME, MIDDLE_NAME, FCN)
SELECT "FIRST", "LAST", "MIDDLE", FCN
FROM DATAGRAB WHERE RECORD_DATE = convert(varchar(8), getdate()-1, 112)
How can I convert it to only update records with a unique FCN value?
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
i have a total of 5 records to update. one being a summary row and the other four being 1,2,3,4 record ids. is there any way to update these 4 records w/o using 4 seperate updates???
Ive posted on here many times before and got the answers, so hopefully this time will be like the rest...you guys are SO good at SQL.
I have a database that has lots of fields. I use some specialised email marketing software that allows me contact each record. What I have done in the past is export the contacts i wanted to email and then format the columns like this: (all done in excel)
EmailAddress Website Salutation Website1 Website2 Website 3 info@eg.co.uk www.eg.co.uk David eg.co.uk Eg.Co.Uk Eg test@red.co.uk www.red.co.uk Sue red.co.uk Red.Co.Uk Sue
After each list looks like this, i then import this data into Access and from there our email software pics up the data and emails accordingly.
Now here comes the problem...
We have decided that we now want everything done within our sql database. Which means we want clickthroughs recording and open rates etc all appearing in our database. (we have all this sorted out)
However we dont have Website1, 2 or 3 fields in our database. We know that they need to be included in our db somewhere but with half a million contacts we don't want to do it one by one obviously.
We want a simple where we export all our contacts and then format them so they include website1, 2 and 3. THEN we want to import them, simply updating the contacts with that new data. (Or is there a SQL way of creating temp tables so these fields dont even need to be included in the db?)
I hope i managed to explain myself in way you can all understand.
I have an Excel file of 1362 rows and 30 columns. I need to load itinto my database to update addresses and phone numbers. Each recordhas its own ID number, which is defined in the Excel file and in thedatabase. The database is Millenium Version 7.2.1.1. I can't reallyfind any signifigance between any of the records. Is there a way I canjust load them into my database and update each record? Maybe by usingthe ID number?Dane WinklerDatabase SpecialistSt Vincent CollegeJoin Bytes!
I'm having a bit of problem putting together a query that will update records in one table from another table. I've got 2 tables lets call them tblA and tblB.In tblA there is EID(int), QID(int), OID(int) and in tblB OID(int) and QID(int). Also there is an input parameter @EID.What I want to have happen is when someone inputs @EID then tblB gets updated from tblA. To give you a heads up there are no PKs or FKs in either of the tables.If there is an OID in tblA it takes the QID from tblA and places it in tblB where OID from tblA and tblB match.Hopefully this makes sense I thought that I could do something like this:CREATE PROCEDURE test3(@EID int)AS UPDATE tblUsedSET QuestionID = (select QuestionID from tblExamQuestions where ExamID = @EID)WHERE OrderID = (select OrderID from tblExamQuestions where ExamID = @EID)GOBut I get an error that says that there are to many records being returned by the subqueries. Winston
I have a table with 5 columns (col1, col2, col3, col4). I want to do is: 1) To check if any two records are duplicates (if the the values in col1 of record A are identical to Record B, two records are considered as duplicates);
2) if two records are duplicate, I want to mark Record B as "Dup" in col4 ;
3) move the data of Col2 of Record B to col3 of Record A.
I have tried to use CURSOR for the job. I would appreciate if anyone can give me some hints for updating records using Cursor.
My script looks like this: CREATE PROC Mark_duplicate AS DECLARE @var1_a, /* To hold the data from Record A */ @var2_a, @var3_a, @var4_a, @var5_a,
@var1_b, /* To hold the data from Record B */ @var2_b, @var3_b, @var4_b, @var4_b,
/*** Create a CURSOR ***/ DECLARE Dup CURSOR FOR SELECT col1, col2, col3, col4 FROM TableA ORDER BY col1, col2 FOR UPDATE OF col1, col2, col3, col4
/**** OPEN the CURSOR ****/ OPEN Dup FETCH NEXT FROM Dup into @var1_a, @var2_a, @var3_a, @var4_a WHILE ( @@FETCH_STATUS =0 ) BEGIN FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, @var4_b WHILE ( @@FETCH_STATUS =0 ) BEGIN If ( @var1_a = var1_b ) THEN . .Updating statements . . ELSE SET @VAR1_a = @var1_b, @VAR2_a = @var2_b, @VAR3_a = @var3_b, @VAR4_a = @var4_b FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, var4_b END END CLOSE DUP . . .
I have a table that has 4+ million records. I need to update those records. I am facing some performance issue. Can someone please advice?
update stage set batch_status = 1 where update_status = 0
Update transaction Set aId = s.aId, b = s.b,
from stage s Where s.aId = transaction.aId and s.batch_status = 1
Update stage Set update_status = 1, batch_status = 2
where
batch_status = 1
When I run the above query with "set rowcount 1000", it runs in one minute. When I run the query for "set rowcount 10000", it runs in 1 hour 56 minutes. Can someone help me to optimize it?
Here's a problem that I can't find anyone else has run into. I'm usingAccess and SQL Server, but the theory would be the same for any db.I have a large number of tables that contain linked records(intersection tables mostly). In the interest of space, I'llillustrate an example:tblStudents (ID, Name)tblTeachers (ID, Name)tblClasses (ID, Name)tblEnroll (StudentID,ClassID,TeacherID)I have about 10 people who each use a separatecopy of this database (in access). I want them (at the end of eachday) to be ableto update all the records that they entered that day into a databasethat I have setup on a server (SQL Server 2005). Both databases havethe exact same structure.Caveat 1: All of the classes, students, and teachers are not the sameon each database, but the server database should contain all of them.Caveat 2: There is no way for the clients to automatically insert intothe server, they are offsite and out of range.Herein lies the problem: when a record is inserted into the server froma client, all of the links are lost since the ID will be different onthe server that it was on the client.I don't think a simple update / insert query will work, and most db'sand languages don't play nice with recordset appends.What are your thoughts??
I have a really simple query I'm trying to execute. I want to replace all instances of int X (8) in Column A (LastActivityID) with int Y (27) but the following SQL returns the said error. I understand the error, but not sure how to script the SQL differently to get the intended result. Thanks in advance.
Code Block
UPDATE Item SET LastActivityID = 27 WHERE LastActivityID = 8 Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have a following problem: I€™m importing records from an Access table into a SQL Server Table. I€™m using lookup to determine if a record already exists in the SQL Server table and in that case I should update the record if it was modified.
I thought of using OLE DB Destination for new records (done, works fine) and OLE DB Command Transformation to update the modified records. It all works but the thing that bothers me is that my table has approx. 40 columns so my OLE DB Command is very long (update table set col1 = ?, col2 = ?, col3 = ? €¦€¦). The other problem is that I€™m always updating all the columns even if only one column was modified.
Is there a better way to update the existing records?
I want to use SSIS in order to synchronize data. The OLEDB and ADO.NET Destination Adapter just inserts the rows read from the source. Since I have PK Constraint on destination, the tranform fails. How can I ask to update the records in destination DB?
I wrote a sproc which does four things: 1. It looks at an option master to see if the record exists before inserting a new one 2. If the record is not there it inserts the optino record 3. Once the record is inserted I have to run a CASE statement on the record to determine its level 4. Once the level is determined, the record needs to be updated with the correct level. 1-3 work fine (when run without the update command). However, even though I set a criteria, UPDATE still updates and not the one records. Any idea why? set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)
BEGIN SELECT ' This option already exists in your Option Master' END ELSE BEGIN
--if the option record option does not exist, insert it
--once the option record is inserted, case it to find the its level (1-9) --update the record with the approciate level.
UPDATE Optionmaster SET optionlevel (
SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND ElevationID > '0' THEN '1' END AS OptionLevel --provides the option level required to update the record FROM optionmaster WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)) --even through I specify the above criteria, it upates all records.
Hello, I am retriving records on a page, I am able to make changes cuase the data is displayed in textboxes, my question is how to save the changes I made in the database. Also in my below code for retriving the data what statement do I need to add so that I can use a QuerryString to filter the results. ex (default.aspx?CID=1) and only records that match that would display. Here is my code: for retriving data Private Sub displayrecord()SqlConnection1.Open() Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT * FROM table1", SqlConnection1) Dim objReader As SqlDataReader Dim SqlCommand1 As New SqlCommand("SELECT * FROM table1", SqlConnection1) SqlDataAdapter1.Fill(dsMember) objReader = SqlCommand1.ExecuteReader() objReader.Read() txtbox_confirmpass.Text = objReader("MemberPassword") txtbox_name.Text = objReader("MemberName") txtbox_birthdate.Text = objReader("MemberDOB") txtbox_email.Text = objReader("MemberEmail") Dropdown_Gender.SelectedValue = objReader("Gender") objReader.Close() SqlConnection1.Close() End Sub Private Sub btnUpdate_Click
I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks.
declare @NextKey int SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS WHERE TABLE_NAME = "tblEmp" set @NextKey = @NextKey + 1
DECLARE tblNewEmp_cursor CURSOR FOR select emp_pk from tblNewEmp
open tblNewEmp_cursor FETCH NEXT FROM tblNewEmp_cursor WHILE @@FETCH_STATUS = 0 begin update tblNewEmp set emp_pk = @NextKey set @NextKey = @NextKey + 1 FETCH NEXT FROM tblNewEmp_cursor end
TRUNCATE TABLE [Temp_Export]; INSERT INTO [Temp_Export] (
[Code]....
The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?
Dear all,I need to update one field in a table for a given record and visit number.Example below is how the table looks -SID VISIT DLCO101 0 12101 1 16102 0 18102 2 10103 1 12103 2 14Here is how I would like it to look. The changes are the starred items.SID VISIT DLCO101 0 14*101 1 16102 0 18102 2 16*103 1 12*103 2 14I know it is an UPDATE statement, but I am not sure how to use it when Ineed to update more than one record.Thanks for the help in advance.Jeff
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 1 SET @RowspPage = 10 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER, * FROM summary ) AS TBL WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage) ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g.  At 12:00pm, the result of page 1 (5 per page)  is R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button Since records R21, R22, R23, R24, R25, R26 are inserted page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
I am seeing a strange behavior in a script component that I can't figure out.
Basically, there is a field in my script that checks to see if a string field is length of 1. If it is, it updates the field with some value. The strange behavior is that it picks up some of the rows but not others.
If Row.FieldValue.Length = 1 Then
Row.FieldValue= "Some Value"
End If
I've tried variations such as: If left(Row.FieldValue.,1) = "-" Then
I currently have a table called stores. I've just added a uniqueidentifier column called store_guid to the stores table. The table currently has about 500 records in it and now i'm trying to set each store_guid = to a newid(). I've tried using UPDATE stores SET store_guid = newid() . however, that doesn't work because i think it's trying to set each record equal to the same guid using that approach. All i need to do is fill in my new column with new guids. any ideas?
I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.
(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249
(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'
It says query executes successfully but returned no rows.
This is a general question regarding the use of view and storedprocedures. I'm fairly new to databases and SQL.I've created a SQL database using an Access Data Project ("ADP") andI'm satified with the table structure. I've moved on to building somefront ends for our users.I'm running into situations where I want subreports to be built fromqueries [views or stored procedures-I don't know which to use so I usethe term query] that are dependent on the values in other controls.I've played with stored procedures and I've figured out how to sendcriteria to a stored procedure and then dynamically change the recordsource of a subreport.However, I'm running into cases where I can't add records to theresults of a stored procedure. The table I'm running a stored procedureon has five fields: (1) Primary Key for each record, (2) FundID that'sa primary key in another table, (3) CompanyID that's a primary key inanother table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute2 of the (Fund/Company).The stored procedure filters the set of Fund/Companies based on aFundID from a form. I can update this stored procedure. However, forusers, they would like to see the Fund Name from the table that hasunique FundIDs. As soon as I include that into the stored procedure, Ican no longer add records.My questions are many:1. Is there a primer online that discusses the theory behind myquestion? Recordsets, updatability, working with recordsets in forms?2. What are some best practices for developing subreports, combo boxes,list boxes, etc. where the data is dependent on the values in a control3. I'm struggling with the best ways to grab objects on a form. If I'mon the main form I'm comfortable working with theMe.__object__.__sub-oject routine. However, if I'm in one subform whereI need another subform to change based on the record I'm in, I feelthat my code to get at the subform is very klunky..forms.main form name.sub form name.form.record sourceI don't even know how I figured out the "form" part before recordsource. Again, are there some basic rules or guides about navigatingthrough forms in VBA?4. Should I be developing front ends in some other environment?I know it's a lot, but all the advice from the newsgroups seems topresuppose some knowledge about how ADP, ADO, ODBC..blah blah and Ican't seem to find any documents about ADP and SQL.
Hi allI just ranUPDATE dbo.tbl_forecastedSET update_ref = 0but it only updated the first 10000 records. I'm wondering if anyone cantell me why this is, and can I get around it.I have looked on google and found a few references, but nothing in too muchdetailsthanks in advanceAndy
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table. 2. I am updating in batches say 20000 record-wise. 3. Changed the recovery mode to bulk logged and auto-growth size is set to  300MB and there is enough space in my disk for transaction log .
I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.
Hi,Currently we're a building a metadatadriven datawarehouse in SQLServer 2000. We're investigating the possibility of the updatingtables with enormeous number of updates and insert and the use ofcheckpoints (for simple recovery and Backup Log for full recovery).On several website people speak about full transaction log and thepace of growing can't keep up with the update. Therefore we want tocreate a script which flushes the dirty pages to the disk. It's notquite clear to me how it works. Questions we have is:* How does the process of updating, insert and deleting works with SQLServer 2000 with respect to log cache, log file, buffer cache, commit,checkpoint, etc?What happens when?* As far as i can see now: i'm thinking of creating chunks of data of1000 records with a checkpoint after the Query. SQL server has thedefault of implicit transactions and so it will not need a commit.Something like this?* How do i create chunks of 1000 records automatically withoutcreating a identity field or something. Is there something like SELECTNEXT 1000?Greetz,Hennie
I am running a DTS Package.I have a temp table with 1494 records. I am inserting a 'Y' or'N'into a temp table #HasClaims.The TempTable name with the Provider Id's(PRPR_ID) is#TempFACETSNODupesThe @identityID is an identity field counting back from 1494 to 1I count back from the Max value of the identityid (1494) in the Whileloop until I get through all the records. The idea is to check for theexistance of a claims and authorization record and put a 'Y' or 'N'record in the temptable #HasClaims.This is running in the Execute SQL Task object of the DTS Package.The Package runs successfully but only inserts 200 rows into the newtemp table. There should be a row for each provider. Each time itruns, the number of rows it returns is different. Sometimes it is 205,then 185, then 210, before it completes the DTS package.Has anyone run into While looping problems within an Execute SQL taskin a DTS package(SQL 2000)like this--------------------------------------------------------------SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupesWhile @identityID >= 1BEGIN@PRPRID is the placeholder for the PRPR_ID (Provider)SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =@identityIDIF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERECLCL.PRPR_ID = @PRPRID)BEGININSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'Y', @identityID)ENDELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'N', @identityID)------------------------------------------------------------SELECT @identityID = @identityID - 1END
If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?
ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?
I use SQLServer2000 when i begin a transaction and update set of records in a table till i don't commit or rollback the transaction nobody can not select previous values of that records and it must wait until i complete the transaction, i have tested it in oracle but it hasn't this limitation don't you think it is Sqlserver weakness?
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!
