Updating Of Existing Records
Sep 4, 2007
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?
Any help is appreciated.
Thanx
Sara
View 4 Replies
ADVERTISEMENT
Apr 23, 2008
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?
Thanks in advance,
View 3 Replies
View Related
Mar 18, 2014
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 ..
View 3 Replies
View Related
Dec 4, 2007
Hi,
I could use some help on how to save the result of my data-flow in an existing table.
I have created a data flow. I have no trouble storing the result of the flow in a new table. But I'm trying to store the result of my data flow in an existing table. (The flow involves fuzzy grouping, and i would like to store the result in the original table on which the fuzzy grouping was performed). But I cannot get it to work.
I can select the existing table as OLE DB destination. I can write an SQL to manipulate this table, but how do i address the result of the flow in the SQL?
Help is welcome here!
View 6 Replies
View Related
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
View 2 Replies
View Related
Jul 21, 2015
I have an existing old SSIS Solution that needs to be updated with MDX query. In the query I am using every thing as is but changing  only the filter condition. When I try to Preview the data on data flow task, I am getting below error.Â
Outputs[OLE DB Source Output] references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.
Package without MDX query update is running fine and no issues. I am using SQL Server 2014 and SSDT 12.0.50318.0 version for your info.
I tried updating SSAS connection string with "Format=Tabular" which was missing earlier and still didn't work.
View 2 Replies
View Related
Feb 26, 2008
I have met the interview question, I provide answer like the following from my experience. I don't know it is correct or need to supplement. Thank you for help.
Question: How to identity existing data for updating in SSIS?
Answer:
If you have the same key columns such as primary key or business key, you just use them to identify existing records from data source to destination.
If you use different key columns between data source and destination, you can create permanent link table which will store business key for data source and destination, and you can compare records from linking table when you update data.
View 1 Replies
View Related
Jul 20, 2005
I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.
View 1 Replies
View Related
Feb 5, 2007
I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?
View 3 Replies
View Related
Jan 19, 2014
I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B
To illustrate:
Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we
[code]....
View 3 Replies
View Related
Oct 7, 2007
hi
I have data in two tables.
NAMES
IDName
1FIRST
2SECOND
CODES
IDCodeTypeCode
1Axyz
1Babc
1Cgfd
2Axdz
2Bdca
I want to join the two tables to add the Code of CodeType "C" to the records of NAMES
Result Example
IDNameCode
1FIRSTgfd
2SECOND----
I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.
how should the sql statement look like?
Please help!
thanks in advance!
Mikk
View 1 Replies
View Related
Jul 2, 2007
Hi,
I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.
I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.
Thanks
Trent
View 4 Replies
View Related
Mar 19, 2007
Good Morning, I need some assistance with SQL Server 2000 Importing Data.
When I import data from a text on a routine basis, three things must happen:
1. New records identified by primary key get appended to table.
2. Exisiting records identified by primary key get overwritten with new/(updated) data.
3. All other existing records are left alone.
Does anyone know how to Import Records with the following the criteria above? It cannot insert duplicate primary keys by nature, so it must overwrite those records!
This is being built into a DTS Package, but I need to get over this obsticle! Thanks for any guidance!
View 2 Replies
View Related
Jul 23, 2005
Greetings:I have a SQL 2000 database, in which about 1% of the records are inlower case. I need to make them UPPER CASE.Is there a function to determine and change the case of existingrecords, or will I have to re-write the records manually?Thanks,DW
View 6 Replies
View Related
Jul 19, 2004
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???
thanks,
e3witt
View 2 Replies
View Related
Sep 6, 2007
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?
View 3 Replies
View Related
Nov 16, 2007
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.
Thanks
View 3 Replies
View Related
Jul 20, 2005
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!
View 4 Replies
View Related
May 19, 2007
Dear All,I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site. Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,Now I want to append 1 more access Table(5500 records) to the existing table having same fields.How to do this.Can any body tell me? Thanks and Regardsmukesh
View 4 Replies
View Related
Jan 31, 2008
Hello. I currently have a website that allows appointments to be booked up for doctors. i currently have an insert record page built using ASP components. I would like to introduce some validation so that if a user tries to book an appointment date and time that is already occupied, an error message is returned saying that appointment slot is already booked. I'm quite confused how to do this. My current code looks as follows.
<%@ Page Language="C#" MasterPageFile="~/AdministratorMasterPage.master" AutoEventWireup="true" CodeFile="AddAppointment.aspx.cs" Inherits="AddAppointment" Title="Add Appointments - Bournemouth and Poole NHS Primary Care Trust" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table style="position: relative">
<tr>
<td style="width: 45px">
<br />
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:SqlDataSource ID="AppointmentsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
DeleteCommand="DELETE FROM [Appointment] WHERE [PatientNo] = @PatientNo" InsertCommand="INSERT INTO [Appointment] ([PatientNo], [PatientSurname], [PatientForename], [ConsultantName], [HospitalName], [Time], [Date], [AppointmentStatus]) VALUES (@PatientNo, @PatientSurname, @PatientForename, @ConsultantName, @HospitalName, @Time, @Date, @AppointmentStatus)"
SelectCommand="SELECT * FROM [Appointment]" UpdateCommand="UPDATE [Appointment] SET [ConsultantName] = @ConsultantName, [HospitalName] = @HospitalName, [Time] = @Time, [Date] = @Date, [AppointmentStatus] = @AppointmentStatus WHERE [PatientNo] = @PatientNo">
<DeleteParameters>
<asp:Parameter Name="PatientNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ConsultantName" Type="String" />
<asp:Parameter Name="HospitalName" Type="String" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="PatientNo" Type="Int32" />
<asp:Parameter Name="AppointmentStatus" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox3" Name="PatientSurname" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="Textbox4" Name="PatientForename" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList1" Name="ConsultantName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList2" Name="HospitalName" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="DropDownList3" Name="Time" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="Textbox2" Name="Date" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DropDownList4" Name="AppointmentStatus" PropertyName="Text" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label1" runat="server" Style="position: relative" Text="Patient No"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox1" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"
Display="Dynamic" ErrorMessage="RequiredFieldValidator" Style="position: relative"
Width="148px">Enter a Patient No</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label6" runat="server" Style="position: relative" Text="Patient Surname"
Width="116px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox3" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="TextBox3"
ErrorMessage="Enter a Surname" Style="position: relative" Width="140px"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<asp:Label ID="Label7" runat="server" Style="position: relative" Text="Patient Forename"
Width="128px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox4" runat="server" Style="position: relative"></asp:TextBox></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="TextBox4"
ErrorMessage="Enter a Forename" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td align="left" style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label2" runat="server" Style="position: relative" Text="Consultant Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
DataTextField="UserName" DataValueField="UserName" Style="position: relative">
<asp:ListItem Selected="True">Select...</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"
SelectCommand="SELECT aspnet_Users.UserName FROM aspnet_UsersInRoles INNER JOIN aspnet_Users ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (aspnet_Roles.RoleName = 'Consultant')"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="DropDownList1"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="152px">Select a Consultant</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label3" runat="server" Style="position: relative" Text="Hospital Name"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="HospitalName" DataValueField="HospitalName" Style="position: relative">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"
SelectCommand="SELECT [HospitalName] FROM [Hospital]"></asp:SqlDataSource>
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="DropDownList2"
ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="136px">Select a Hospital</asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<br />
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label4" runat="server" Style="position: relative" Text="Appointment Date"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:Calendar ID="Calendar1" runat="server" Font-Size="Smaller" Style="position: relative" OnSelectionChanged="Calendar1_SelectionChanged" OnDayRender="Calendar1_DayRender">
</asp:Calendar>
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:TextBox ID="TextBox2" runat="server" Style="position: relative; left: 0px; top: 8px;" Width="256px"></asp:TextBox><br />
</td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox2"
Display="Dynamic" ErrorMessage="Enter a Date" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px; height: 26px">
</td>
<td style="width: 136px; height: 26px">
<asp:Label ID="Label5" runat="server" Style="position: relative" Text="Appointment Time" Width="132px"></asp:Label></td>
<td style="width: 47px; height: 26px">
</td>
<td style="width: 100px; height: 26px">
<asp:DropDownList ID="DropDownList3" runat="server" Style="position: relative; left: 0px; top: 0px;">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem Value="09:00">09:00</asp:ListItem>
<asp:ListItem>09:30</asp:ListItem>
<asp:ListItem>10:00</asp:ListItem>
<asp:ListItem>10:30</asp:ListItem>
<asp:ListItem>11:00</asp:ListItem>
<asp:ListItem>11:30</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px; height: 26px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="DropDownList3"
Display="Dynamic" ErrorMessage="Select a Time" Style="position: relative"></asp:RequiredFieldValidator></td>
<td style="width: 100px; height: 26px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<br />
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
<asp:Label ID="Label8" runat="server" Style="position: relative" Text="Appointment Status"
Width="136px"></asp:Label></td>
<td style="width: 47px">
</td>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList4" runat="server" Style="position: relative">
<asp:ListItem>Select...</asp:ListItem>
<asp:ListItem>Booked</asp:ListItem>
<asp:ListItem>Modified</asp:ListItem>
<asp:ListItem>Patient Notified</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Select a Status"
Style="position: relative" Width="120px" ControlToValidate="DropDownList4"></asp:RequiredFieldValidator></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 45px">
</td>
<td style="width: 136px">
</td>
<td style="width: 47px">
</td>
<td align="center" style="width: 100px">
<asp:Button ID="Button1" runat="server" Style="position: relative" Text="Submit" OnClick="Button1_Click1" /></td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr></table>
</asp:Content>
Any help would be very much appreciated.
Thanks,
James.
View 2 Replies
View Related
Jun 9, 2005
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
View 11 Replies
View Related
Mar 8, 2000
Hello Everyone,
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
.
.
.
Thanks a lot. Have a good day!
Lunjun
View 2 Replies
View Related
Aug 30, 2006
Meg writes "Hi,
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?
Thanks.
Meg"
View 4 Replies
View Related
Mar 28, 2006
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??
View 1 Replies
View Related
Dec 12, 2007
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.
View 10 Replies
View Related
Jul 29, 2006
Dear All,
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?
Regards,
Sassan
View 4 Replies
View Related
Nov 29, 2006
Hi there!!
I have a table say Emp with index as (Empid) which is a autoincreament field.
How do I update names of already existing rows having identified Empid.
Complexity lies with the fact that I cannot use Emp table as Destination table because if i do so then it will insert new rows and empid will change.
Thanks and Regards
Rahul Kumar, Software Engineer
View 4 Replies
View Related
Sep 13, 2007
Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields.
How do i update the row coming from the transaction file in the tables which contain the rows from the master file .
to better explain the example is
Master File
ID Name AGE Salary Transcode TransDate
2 dev 27 2777
Transaction File indicating change
ID Name AGE Salary Transcode TransDate
2 dev 27 24444 C 08072007
the ouput should be
2 dev 27 24444 C 08072007
replacing the existing row in the table(updating the whole row)
i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row.
what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row.
Thanks...
View 1 Replies
View Related
Feb 16, 2007
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
--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]
@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit
AS
--check to see if the option record exists
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
INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )
SELECT ' Added to Option Master Successfully'
END
--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.
View 9 Replies
View Related
Feb 15, 2008
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
View 2 Replies
View Related
Jun 21, 2000
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
close tblNewEmp_cursor
View 1 Replies
View Related
Jul 27, 2012
I have the following code:
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?
View 6 Replies
View Related
Jul 23, 2005
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
View 3 Replies
View Related
