SQL Table Lookup Methods
Aug 29, 2006
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()
Dim myfileout As StreamWriter
myfileout = File.CreateText(Server.MapPath("out.txt"))
myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")
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
View 2 Replies
ADVERTISEMENT
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
Mar 8, 2006
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
Which is the best? Thanks!
View 3 Replies
View Related
Apr 1, 2014
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):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
View 7 Replies
View Related
May 4, 2006
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.
View 3 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
Jan 29, 2004
Hi
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.
Cheers
Jon
View 2 Replies
View Related
Feb 23, 2006
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
View 6 Replies
View Related
May 12, 2008
Table 1
Skill_ID SkillName
1 xml
2 oracle
3 sql
4 vb
Table 2
EMP_NAME SKILL_ID_1 SKILL_ID_2 SKILL_ID_3
JOHN DOE 1 4 null
JANE COLE 3 4 null
SAM COOK 1 2 4
I want to return the following:
RETURN DATA
EMP SKILL_ID_1 SNAME1 SKILL_ID2 SNAME2
JOHN DOE 1 xml 4 vb
JANE COLE 3 sql 4 vb
SAM COOK 2 oracle 3 sql
View 1 Replies
View Related
Jul 11, 2013
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...
View 5 Replies
View Related
Jul 9, 2006
I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..
Table City:
CityID CountyID CountyName
1 3 NULL
2 2 NULL
3 1 NULL
Table County:
CountyID CountyName
1 Los Angeles
2 Contra Costs
I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.
Barkingdog
View 1 Replies
View Related
Jun 26, 2007
I have another lookup table question
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)
Department
DeptName (pk) unique
thanks all
View 3 Replies
View Related
Mar 23, 2008
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.
View 8 Replies
View Related
Feb 13, 2007
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.
Any clue ?
Thanks
Fahad
View 7 Replies
View Related
Oct 26, 2006
Hi there
I'm getting into trouble everytime I want to setup a dataflow containing 2 or more flows, where I want to make a lookup into 1 other table.
This fails, of some reason I do not know of.
ex.
Table 1 -> Lookup Currency Table -> Table2 with currency
Table 3 -> Lookup Currency Table -> Table 4 with currency
One of the lookup will fail.
How do you avoid this?
/Bjarne
View 1 Replies
View Related
Apr 21, 2015
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
View 4 Replies
View Related
Sep 23, 2015
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.
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
May 1, 2007
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!
Table DDL:
CREATE TABLE [dbo].[EXTERNAL_FUNDING] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[project_id] [int] NOT NULL ,
[date_requested] [datetime] NULL ,
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [int] NULL ,
[amount] [money] NULL ,
[granted] [bit] NULL ,
[date_granted] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[EXTERNAL_FUNDING_TYPE] (
[type] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
View 2 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Aug 13, 2007
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
View 5 Replies
View Related
Feb 26, 2008
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.
View 1 Replies
View Related
Aug 5, 2007
Hi,
I'm trying to figure out how to setup a Constraint (Foreign Key?) from a lookup table.
For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.
Any thoughts?
Thanks!
Andy
Here's the scenario:
CAR table:
Id (PrimaryKey)
LicensePlateNumber
ParkingSpotNumber
PARKINGFACILITY__CAR__LOOKUP table:
Id (PrimaryKey)
CarId (Foreign Key to Car.Id)
ParkingFacilityId (Foreign Key to ParkingFacility)
PARKINGFACILITY table:
Id (PrimaryKey)
PARKINGLOT table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
PARKINGGARAGE table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
Here's the T-SQL code to make a SQLServer2005 database for this scenario:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Car](
[Id] [bigint] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ParkingSpotNumber] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](
[Id] [bigint] NOT NULL,
[CarId] [bigint] NOT NULL,
[ParkingFacilityId] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingLot](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingGarage](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED
(
[Id] ASC
)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]
View 4 Replies
View Related
Sep 21, 2007
I need some help with the following...
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?
View 10 Replies
View Related
Jun 28, 2006
Hi,
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?
Thanks in advance,
Lawrie
View 8 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
Mar 11, 2013
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-
View 7 Replies
View Related
Jun 21, 2015
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)
View 13 Replies
View Related
Oct 18, 2005
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.
View 1 Replies
View Related
Mar 21, 2008
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?
My Source Table
--------------------------
Citye Type
City Name
Source
New York
Via City1
Pittsburg
Destination City
Chicago
Source
New York
Via City1
Philadelphia
Destination City
Chicago
My Destination
------------------------
Master Table
ID
1
2
Detail Table
Master Table ID
Citye Type
City Name
1
Source
New York
1
Via City1
Pittsburg
1
Destination City
Chicago
2
Source
New York
2
Via City1
Philadelphia
2
Destination City
Chicago
Thanks
View 11 Replies
View Related
Oct 8, 2007
Hello,
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 - - - - - - - - ->
Please advice.
Thanks.
View 3 Replies
View Related
Jul 5, 2006
Hi there!
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?
Many thanks in advance!
Wolfgang
View 2 Replies
View Related
Jun 27, 2007
Hi All,
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.
Lets assume DimensionA Structure
id
Description
StartDate
EndDate
Fact Table
id
measure1
measure2
TimeId
Description
Time Dimension
TimeId
Date
Day
Hour ...
View 1 Replies
View Related
