I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.
What would be the more efficienct method to use?
'Dim myItemData As New DataReader
Dim myItemReader As SqlClient.SqlDataReader
Dim myItemCommand As New SqlClient.SqlCommand
Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx")
myItemCommand.Connection = myConnection
myItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"
myItemCommand.Connection.Open()
myItemReader = myItemCommand.ExecuteReader()
While myItemReader.Read
If myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then
'what method to to look up for example the category name using the category_quid referenced in the item master
'Write out record for parts file
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?
In order to update a MSSQL table, named [Table_1]. The procedure is: (@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1: UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id Method 2 IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id Method 3: DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date 01 32 London 01-01-2013 02 35 Lagos 02-01-2013 03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type 11 AGE col_integer 22 City col_character 33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
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.
I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.
In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:
1) Is it possible to perform a JOIN on a view in the way that I would need to here?
2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?
Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.
Hi, I'm inserting records into a Table1 with the following fields : tabID int 4ClientID int 4..etc etc. In the insertitem template, I want the user to enter a Clientcode into textbox1. Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1. I've got a mental block and I cannot see how I can do that ! Any ideas most gratefully appreciated. Thanks Sunil
I have a table "Ratings" in which I store rating categories like the following:
FROM TO Rating 1 10 1 11 20 2 21 30 3
In another table "Results" I have the actual results like:
Employee ID Results Rating 120 6 ? 130 18 ?
Now I would like calculate the rating in the results table in the rating column (where "?" in the above example)
How can I do that with an SQL update command - maybe using a Case statement - but I don't know how to do that by looking up values from another table...
so say i have an employee table, and a department table. all the department table will do is store the names of the different departments so how should this be set up using MSSQL
Employee EmployeeId FN LN DeptName (fk)
Department DeptId (pk) unique DeptName unique
*** Or could i do this to make the employee record more meaningful without needing a join. ***
Employee EmployeeId FN LN DeptName (fk)
Department DeptId (pk) unique DeptName unique
*** Or should i just do this and get rid of the Id Field*** Employee EmployeeId FN LN DeptName (fk)
i have very very basic knowledge of SQL like the ability to add, delete, update, select records and the ability to drop/create tables but i want to know how to integrate a lookup table into a field (very much like MS Access users can do with ease).
here is my example;
i have two tables - Club and Player
Club:
Club_Id ClubName Abbr
Player:
Player_Id Name Club_Id
the idea is that a single club can have several players. in the players table, i want the Club_Id field to act as the drop down whereas if a player changes club at any time, i can just refer to that player and change his Club_Id by scrolling down a list of values in SQL Server Management Studio 2005.
how can this be achieved by any chance, help would be very much appreciated.
Hello, I have few columns at some level, and now I need to add a column from another database table based on the values from one of the existing columns.
It seems that Lookup Transformation doesnt support parameterized query.
I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'
My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -
SELECT * FROM Sandbox.dbo.Sheet1$ A LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B ON A.[Street 1] = B.address1_line1
Say I want to lookup a value in another dataset, but there is a grouping that requires you to know what the values for each level is in order to get to the correct detail record. Can you still use the lookup function with more than one field to compare against? So for example
Department \___SalesPerson \___Measure
I want to be able to add a new row at the Measure level, but lookup each field from another dataset. In order to do that I will need the Department AND SalesPerson values to do the lookup, but I dont think the Lookup function will let us do that will.
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:
I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use: SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted] FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE WHERE (project_id = @project_id) AND (EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id) ORDER BY date_requested ,source,type
But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!
What is the best way to setup relationships between one lookup tableand many other tables. The tables did not have any lookup tablerelationships which I am adding. One lookup table is used for same datain several different places.To use one lookup tables with several tables, I had to disable "CascadeUpdate" and only have "enforce relationships for updates and inserts"checked.Any pros/cons?Thanks in advance.P
I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.
It does show up in the preview.
Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?
In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor
Can anybody tell me when the index table is created? I have a Ole DB Command transformation that deletes rows from a table, and the pipeline continues on to a fuzzy lookup. The fuzzy lookup is returning matches to some of the rows which were delted in the aformentioned Ole DB Command. If the index table is created during pre-execute, this would make some sense to me since those rows which get deleted still exist before the pipeline reaches the Ole DB Command transformation which deletes those rows. If this is the case, is there a way to delay the index table creation? If this is not the case, has anybody else ran into anything like this...is there some solution? Thanks for any help.
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])
REFERENCES [dbo].[Car] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))
ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))
ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data: key size height 1 'Small' 'Tall' 2 'Big' 'Short' has to become 1 'Y' 'P' 2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest Example: col_name vl_source vl_dest size 'Small' 'Y' size 'Big' 'N' height 'Tall' 'P' ... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
I have a common requirement (when I'm processing data rows from an input file) to perform some data manipulation in script then look up a value from a reference table and perform some further data manipulation depending on whether a matching value was found in the lookup table or not.
For example, say I'm processing Customer data rows and the first "word" (/token) of the FullName column might be a title or the title could be missing and it might be a forename or initial instead. I want to check the first word of this FullName column to see if it matches any valid title values in a ReferenceTitles lookup table. If I find a match I want to set my Title column to the value from the ReferenceTitles lookup table, otherwise I want to set it to, say, an empty string. Then I want to process the rest of the FullName column tokens differently depending on whether or not a match was found.
It seems very messy to start coding a script transformation and then have to use a lookup transformation combined with a script transformation on the error output followed by a union and a sort and finally a further script transformation (especially as I would like to be able to use variables from the first script in the later processing)...
So what I'm wondering is: Is there an easy/clean way to perform a database lookup (using cached values) from a script so that I can achieve all the above from within a single script component?
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.
Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.
Hosting_Site (Source File) YEAR MONTH HOSTING CLASS REGION HOSTING COST 2015 1 A UK 75 2015 1 A IN 80 2015 1 B IN 60 2015 1 C US 100
BENCHMARK (Lookup Table) Row Year Month Hosting class Region BENCHMARK COST 1 2015 1 A US 100 2 2015 1 B US 200 3 2015 1 A UK 50 4 2015 1 A null 70
STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)
Row Year Hosting class BENCHMARK COST 1 2015 A 22 2 2015 B 33
Then result should be:
FACT LOAD:
YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST 2015 1 A UK 75 50 2015 1 A IN 80 70 2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used) 2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)
I have found the Common Table Expressions described in SQL 2005 and Iam not sure if it applies to this situation.Here are the tables<PRE><B>ManagedServer Table</B>--IdManagedServer (PK, int, not Null)--Name (nvarchar(256), not null)<B>ManagedServerToManagedServer Table</B>--IdParentManagedServer (PK, int, not null)--IdChildManagedServer (PK, int, not null)</PRE>The following will give you the parent-- Get Managed Server Group NamesLEFT OUTER JOIN ManagedServerToManagedServer mstms ONms.IdManagedServer = mstms.IdChildManagedServerLEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =msg.IdManagedServerHow would you go about getting all of the "parents" in the tree?Can this be done with CTEs? Unfortuately all of the examples found arejoining on itself.
Hi I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?
The illustration below is for a customer dedupification project. The Source file, containing customer name and address records, is conditionally split based on 7 ranges of substring(city,1,2) to distribute the load across 7 different threads for parallelization. Each customer record in the source file is looked up against a reference table named Location_Stage for its existence using the Fuzzy Lookup transformation.
The reference table Location_Stage has around 10 miilion+ records. The source file would normally have around 1 million records.
I am wondering :
- if it would be possible to partition the Match Index of the reference table (NOT the reference table) into 7 partitions based on 7 ranges of substring(city,1,2) and maintain these partitions on different drives? - if it is possible to specify a particular partition to be used by a FzLkup transformation? - if the partitioning approach will improve the performance of the Fuzzy Lookups?
Source File Feed | Split data into 7 groups based on substring(city,1,2) | ------------------------------------------------------------------------------------------------------------------------------------------ | | | | | | | UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll | | | | | | | FzLkup FzLkup FzLkup FzLkup FzLkup FzLkup FzLkup | | | | | | | Split Split Split Split Split Split Split | | | | | | | ------------- -------------- -------------- -------------- -------------- -------------- -------------- | | | | | | | | | | | | | | <- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->
I have some troubles getting done a lookup... data i get from a legacy system has to be cleaned up and split up into normalized form. i get invoice data and want to split up invoices into a separate table as customers. therefore i lookup if the customer of the current line is not already in my newly created customer-table. if it isn't there (error output of lookup transformation) i insert it into the customer-table.
The problem is that after the customer is inserted into the customer-table, it still is not found by the lookup transform because the lookup uses caching to hold the reference table (and so is the exact same customer inserted again and again and again). Is there any way to disable caching and let the lookup transformation do a select for every new row it gets? or at least refilling the cache if some event happens?
Actually this is in regard to SCD Type 2 Dimension, Scenario is like that I am moving Fact table from some old source and I have dimensionA description value in fact which I want to replace with appropriate id from Dimension Table and that Dimension table is SCD Type 2 based on StartDate and EndDate and Fact Table doesn't contains direct date value rather there is timeId in Fact so to update the value in Fact table I have to Join Time Dimension table and other Dimension Table to replace fact Description with proper Id.
