gkaindia.com

Home > > How to lookup index

How to lookup index

Advertisement
I'm very new to SQL Server and especially relational data so the question I have is very complex for me, but it's something I've been tasked with. I have a small database acting as an infrastructure inventory into which I'm importing data. The database has
a table called 'vcenters' storing all vcenters and the primary key from this links to the foreign key in several other related tables (e.g. hosts, VMs, Clusters).
CREATE TABLE [dbo].[vcenters](
[vCenterID] [int] NOT NULL,
[vCenterName] [varchar](20) NOT NULL,
[CustomerID] [varchar](10) NOT NULL,
CONSTRAINT [PK_vcenters] PRIMARY KEY CLUSTERED
[vCenterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[vcenters] WITH CHECK ADD CONSTRAINT [FK_vcenters_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
So the vcenters table contains an numeric index field which is auto-incremented each time data is inserted (1,2,3 etc) and a name field. When I go to import the name into one of the other linked tables I instead need to look up the index from the vcenters
table and insert the numeric value but I don't know how to do this?
CREATE TABLE [dbo].[Hosts](
[HostID] [int] NOT NULL,
[HostName] [varchar](25) NOT NULL,
[vCenterID] [int] NOT NULL,
CONSTRAINT [PK_Hosts_1] PRIMARY KEY CLUSTERED
[HostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Hosts] WITH CHECK ADD CONSTRAINT [FK_Hosts_vcenters] FOREIGN KEY([vCenterID])
REFERENCES [dbo].[vcenters] ([vCenterID])
GO
I understand inner joins but if anyone could help with this then I'd appreciate it. If I'm not making myself clear then please say where and I'll elaborate. Oh, and I'm using SQL Server Express 2014 (as a testbed).
Thanks in advance
Adam
Advertisement
You never mentioned what your source is but if you are hand writing inserts or generating them you could do something like this.
INSERT INTO Hosts (HostName, vCenterID)
VALUES ('MyName',(Select vCenterID from vcenters WHERE vCenterName = 'yourName'))
This does make the assumption that vCenterName is a unique field, to be sure of it I would but a unique index on this field on the table. Also your DDL is missing the key word IDENTITY after your primary key columns, this lets Sql Server auto increment the
id with each new record and assign it as the value. It is probably a good idea to use this for Hosts as well.
CREATE TABLE [dbo].[vcenters](
[vCenterID] [int] IDENTITY NOT NULL,
[vCenterName] [varchar](20) NOT NULL,
-- additional columns
CONSTRAINT [PK_vcenters] PRIMARY KEY CLUSTERED
[vCenterID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
-- unique constraint on vcentername
CREATE UNIQUE NONCLUSTERED INDEX [IX_vcenters_vecenterName] ON [dbo].[vcenters]
[vCenterName] ASC
GO
-Igor
Read the other 3 answers
The custom xquery function dbxml:lookup-index seems broken for edge-element-presence indices when a namespace is specified: dbxml> lookupedge edge-element-presence 'http://ceridwen.us/default' node11 'http://ceridwen.us/default' node1 1 objects retur
Hi, Situation: I have a series of data in an arraylist. The user may input several variables and according to these variables, a calculation will be performed on the data in the arraylist. E.g. array = [1 2 3 4 5 6] //fixed variables of which data 1
Hi Folks, does anyone know if Forte has a Master Index for ALL the Forte Manuals. I'm of cause looking for something similar to the "OpenVMS Master Index" manual. If the same does not exist for Forte it would sure be appreciated - well at least
I've been writing a program to deal with demographic data and the first server it calls returns an xml string. However, the backup server returns an html string instead of an xml string, so the formatting is a bit different. I was going to use a rege
Please help me with this problem: I have a complex Excel sheet, with functions like VERT.LOOKUP, INDEX etc. This should work in Xcelsius, but I have problems when having a preview in SWF. In the Xcelsius Present 2008 drawing window, all buttons work
Identification Diameter Soak 3" Tank (inches/kft) AWG mils Code Strands Shield Conductor Insulation Semi-Con Days SD Injection Soak Total 2 175 00 7 External 0.288 0.692 0.752 60 0.4 3.6 20.0 23.6 2 220 00 7 External 0.284 0.764 0.864 75 0.4 3.6 20.0
I'm trying to use the following expression:      for $d in dbxml:lookup-index($container, trace($doctype, "find-linked-type")) it's in a module which I import into my query. Used from the dbxml shell, it works great. The namespace it uses there
Hi all, I want to use Vector position as a key of hash map. Do you think that it is work? And could you mind show me some sample code. Thanks!!!It'll work. Here's some code that follows. However, in my sample, I'd rather store 'user' as the key to th
Hello, I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to s
Revision: 4982 Author: [email protected] Date: 2009-02-16 23:31:05 -0800 (Mon, 16 Feb 2009) Log Message: Moving DataGroup from item based lookup to an index based lookup. Also, we now cleanup old item renderers when the dataProvider changes. This sol
Hi. I already posted related problems. As one of my customers get other problems, I come back with my philosophical threads... So: got a first parent list; a child list referencing parent list using 'X' lookup column. If I got X indexed: filtering th
Hi All, We are facing performance issue on executing a task WC_SIL_APInvoiceDistributionFact, this takes 1:20:13mins to complete the load. Issue is with the Lookup table W_EMPLOYEE_D that takes around 35mins, so we have increased the data and index c
Revision: 5002 Author:   [email protected] Date:     2009-02-18 16:33:42 -0800 (Wed, 18 Feb 2009) Log Message: Fix up the lookup in DataGroup when finding the appropriate index in the dataProvider given a particular renderer. With this fix, we now co
I'm trying to filter a large list that has more items than the list view threshold. The list has a lookup column that is indexed. If I  try to filter by other columns, the view works fine as long as it returns fewer than 5,000 items. However, if I tr
Hi all, we have upgraded to SQL Server 2008 R2 from SQL Server 2008 and since then our fuzzy matching process has failed when trying to re-use existing index with the error: [Fuzzy Lookup] Progress: Warming caches - 0 percent complete [Fuzzy Lookup [
Hi Everybody, I am on OWB 10gr2. If we need to use lookup on source table, do we bring the lookup table to stagging area? As we stage table/view for look up need on OWB, do we build our own index in stagging schema? Do we need to match OWB index with
Hey all,  I'm new to using numbers (any spreadsheet really).  I can't figure out how to make this work. What I want to do for example is lookup a weight value in (F) by matching all the criteria in (A),(B),(C),(D) & (E).  If possible I would like my
The best way to retrieve a specific document seems to be using XmlContainer::getDocument, thus using the default index. However, I may not have the document name, but instead an ID I have defined a unique index for. Of course, I can use the XQuery in
Simple question ... and I feel somewhat stupid for asking ... but I'd really like to make an effort here to take advantage of view object caching as opposed to killing our database. Situation: I have a lookup table called CodeValue which has two fiel