Using A Lookup To Update Existing Records
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
ADVERTISEMENT
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
Aug 31, 2015
I have two records in the source with information ID, RevisionID, Description, Region
There are two lookup files one with ID,Description amd other with ID, Region
I wish to update my two source records with performing lookup with these two files.To get the correct description and region data. How to do this in ssis DFT.
View 4 Replies
View Related
Sep 4, 2006
Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
View 1 Replies
View Related
Aug 9, 2007
Hi All,
I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA
The line in the import text file like this:
00000023450000012345
in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB
In the Derived Column transformation,
set the column name IDA as expression: SUBSTRING(LINE,1,10)
set the column name IDB as expression: SUBSTRING(LINE,11,10)
In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.
The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.
In the sample above, if i run the sql in the SSMS:
Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'
there is one record retrived.
Any idea?
TIA
View 9 Replies
View Related
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
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
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
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
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 23, 2005
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 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
Feb 9, 2006
Hi i have three tables
T1 is a table of data imported by DTS, it has staff numbers and location names
T2 is a table that contains system data about staff and has a column for the location id
T3 is a table for the location names and their ID's
i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.
so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location
the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:
Thanks
FatherJack
View 2 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
Nov 4, 2015
I have an Excel file which contains some data. I want to load that into a SQL server Table. Here are my conditions :
1. If the table doesn't have any matching records from the Excel file, then my DFT should load the data from that Excel to the Dest Table.
2. If the table has even one or more matching records, then the DFT should not process at all, instead I should send an email to the business stating that there are some matching records and hence the package is not process...ed.
P.S. If i use Lookup, I have two matching and non-matching output. which will process the non matching records into the table and matching can be redirected to any flat/Excel file. But i don't want to do this. I just want to lookup the Sql Server table and excel.
It'll be good if there is an additional option in the Lookup "Fail component on matching records".
View 3 Replies
View Related
Mar 5, 2005
I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.
For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.
The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same????! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.
Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?
Or is there a command that tells MSSQL to ignore all relationships for the next query?
Any ideas?
View 1 Replies
View Related
May 15, 2006
I need to create an SSIS package that updates columns in a table from columns in another database where the keys match. What's the best way to do this?
View 3 Replies
View Related
Dec 6, 2006
IUpdating existing rows in a SQL server 2005 destination table
--I have a CSV file and every row needs to perform an update on a production table
The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ???
That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow??
thanks in advance
Dave
View 6 Replies
View Related
Jan 4, 2005
All,
Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.
We can certainly write this but before reinventing the wheel I figure I'd ask and see.
Many Thanks,
Isaac
View 14 Replies
View Related
May 21, 2002
I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?
I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David
View 4 Replies
View Related
Feb 6, 2007
HelloI used to work in a Sybase database environment. When I had to insert/update records in the database, I always used "insert on existingupdate", in this way, you didn't have to check whether a recordalready existed (avoid errors) and you were always sure that afterrunning the scripts, the last version was in the database.Now I'm looking for the same functionality in MS SQL Server, asked afew people, but nobody knows about such an option.Does anybody here knows the SQL Server counterpart of "insert onexisting skip/update"? If this doesn't exist, this is a minus forMS ;).Greetz,Bart
View 4 Replies
View Related
Mar 4, 2008
Hi,
I have an example situation that seems like it should have a super easy solution, but my jobs keep failing.
Here we go. . .
I have a SQL Server 2005 table as my source in a data flow task.
This table contains raw data.
We'll call it FACT_Product_Raw - which contains a field called ProductType varchar(1)
Let's say that ProductType contains values of "A" or "B" or "C" - or for that matter, some null and garbage values
I have a lookup table, LOV_Product_Types
This table contains 3 fields that will transform my raw data table
We'll call these fields ProdTypeID smallint, ProdTypeRaw varchar(1) and ProdType smallint
It contains pairs such that A = 1, B = 2, and so on.
Here's what I want to do.
I want to ADD a field to FACT_Product_Raw that contains the "looked up" value from LOV_Product_Types.
Let's say that I want to add the ProdTypeID field to my _Raw table.
I have used the _Raw table as both my source and destination
It blows up every time.
Help.
Thanks,
David
View 5 Replies
View Related
Jan 15, 2015
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
OLD FIELDNEW FIELD
B123F
B123H
B123T
OLD FIELDNEW FIELD
B123F
B123H
B123T
View 2 Replies
View Related
Feb 4, 2006
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
View 1 Replies
View Related
May 12, 2004
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
View 4 Replies
View Related
Mar 16, 2015
I am trying my first bulk update to an existing SWL table from a CSV text file,The text file naming is exacrtly the same as the SQL table, with the same attributes
The statements:
BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:Baanjedox_dailyjdcom4401.txt'
WITH
[code]....
The error message is:
[size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]..The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.
View 5 Replies
View Related
Mar 17, 2015
Have installed SQL Server 2008 R2 Express (includes SSMS tool) on Windows server 2008 R2 sp1 without any issues.Database created with no issues, full text catalog created via the wizard also with no issues but cannot run the process as a scheduled task of updating the catalog because the SQL agent is not available in the express version.
The full text index information is already being populated and updated by a third party application so this leaves just the catalog to be updated as and when new full text information is available.
I have a third party SQL scheduler which will run SQL scheduled tasks but requires a script to run the full text catalog update process
Is it possible to extract a script from the existing full text catalog to run the update process or how to create a script from scratch to do the same update catalog process in the third party scheduler?
View 1 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Oct 31, 2007
We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.
We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).
After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.
We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.
Does that performance seem inline with expectations? Any thoughts to improve performance?
View 4 Replies
View Related
Sep 26, 2007
I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.
It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:
Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.
These errors occur regardless of what columns I am attempting to add to the lookup list.
I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.
Any ideas on what else could be causing this?
View 4 Replies
View Related
