title: SQL - Write Query with field names and table name stored in a table?
body: <p>I work for an Insurance Company and creating a custom application. When we apply a coverage to a policy it is stored in a table with a Limit, Deductible, and Premium field.  However, there are many coverages that do not follow the norm.  They use different fields for Limit, Deductible, and Premium and are even stored in different tables.  (Don’t ask why and don’t even try to understand the fact that they are STILL doing this so I need to leave logic to allow for additional coverages like this.)  There is also no rhyme or reason to what fields they choose so varies greatly.  </p>
  
  <p>So, we have decided to create “LookUp” tables that will store all the information we need for a coverage.  This avoids numerous joins – we were having issues with speed and performance of the application.  </p>
  
  <p>I need to find a way to prepopulate all these fields into a table.  I would like the best option performance-wise.  This will end up being a Nightly job – so when we receive a new snapshot of data we will run this query and prepopulate/update the values of the table.  </p>
  
  <p>Here is an example of my table, it contains the Coverage  name (Coverage), the table (File), and the fields for the Limit, Deductible, and Premium.  The PolicyNum field is a concatenation of LOB + Policy + Module.  I need all three of these fields to join to a policy.  I wanted to use a table since they will be adding new coverages.  Keep in mind, I created this tables so can add more fields to it.  </p>
  
  <p>Here is the create script for my table - I fill it from an Excel spreadsheet so made all the fields nVarChar(255) in order for that process to run smoothly:</p>
  
  <pre><code>USE [PolicySummary]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[Coverage](
      [CoverageID] [int] IDENTITY(1,1) NOT NULL,
      [LOB] [nvarchar](255) NULL,
      [Coverage] [nvarchar](255) NULL,
      [Description] [nvarchar](255) NULL,
      [File] [nvarchar](255) NULL,
      [Limit] [nvarchar](255) NULL,
      [Deductible] [nvarchar](255) NULL,
      [Premium] [nvarchar](255) NULL,
      [IsMultLimit] [bit] NULL,
      [IsMultDeductible] [bit] NULL,
      [IsMultPremium] [bit] NULL,
      [InsuranceLine] [nvarchar](255) NULL,
      [PolicyNum] [nvarchar](255) NULL,
   CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED 
  (
      [CoverageID] 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
  </code></pre>
  
  <p>So in a nutshell, a policy may have the coverage BP0713.  I then to look at this table and see that I need to run the following query:
      SELECT BYAGVA as limit, BYPPTX as Deductible, BYA3VA as Premium FROM ASBYCPP WHERE BYARTX+BYASTX+BYADNB = @PolNum</p>
  
  <p>The table contains the table name, the 3 fields, and the concatenation for the Policy Number.  </p>
  
  <p>Currently, I am looping through each coverage in a policy and selecting the values one at a time in a WHLIE Loop.  Works but it is slow and I am hitting the query numerous times just to open the app (numerous units per policy so will be ran for each unit).</p>
  
  <pre><code>SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' +     ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.'     + [File] + N' WHERE ' + PolicyNum + N' = ''' + @PolNum +'''' + N' AND ' + @CovField +     N'='''+Coverage +''''
  FROM PolicySummary.dbo.Coverage 
  WHERE Coverage = @Coverage 
  AND LOB = @LOB           
  
  EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255)         output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
  
  INSERT INTO #OptCov2 VALUES(@Coverage, @Description, @LOB, @Limit, @Deductible,     @Premium)
  </code></pre>
  
  <p>Can I get help with finding a way to populate my “Lookup” table each night so all I would have to do is 
      SELECT Limit, Deductible, PREMIUM FROM CoverageLookUp WHERE PolicyNum = @PolicyNum</p>
  
  <p>I am using Microsoft SQL Server Management Studio. </p>
  
  <p>Here’s what I’ve got so far.  It takes nearly 4 minutes to run the top 1000 records and there are a total of 614,497 records to run.</p>
  
  <pre><code>SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' +   ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N'     FROM PHI_PIJ.dbo.ASBYCPP' + N' WHERE ' + PolicyNum + N' = ''' + @PolicyKey +'''' + N' AND BYAOTX' + N'='''+Coverage +''''
  FROM PolicySummary.dbo.Coverage 
  WHERE Coverage = @Coverage 
  AND LOB = 'BOP'         
  
  EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output              
  
  UPDATE PolicySummary.dbo.CoverageLkup
  SET Limit = @Limit, Deductible = @Deductible, Premium = @Premium
  WHERE CovAbbrev = @Coverage
  AND PolicyKey = @PolicyKey
  </code></pre>