SQL - Write Query with field names and table name stored in a table?
-
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. 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. 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. 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. Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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\] GOSo in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need t
-
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. 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. 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. 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. Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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\] GOSo in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need t
Where is the table? :confused: It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
-
Where is the table? :confused: It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
I have edited my question to include the script I use to create the table. Sorry about that.
-
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. 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. 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. 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. Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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\] GOSo in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need t
Don't forget to create some indexes too, in order to speed things up.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I have edited my question to include the script I use to create the table. Sorry about that.
Ah, good, how about some sample data? Made up values would be fine of course.
You'll never get very far if all you do is follow instructions.
-
Ah, good, how about some sample data? Made up values would be fine of course.
You'll never get very far if all you do is follow instructions.
I had issues pasting my entire query so I just pasted the piece inside the loop. It works but seriously takes ways too long. I need to find a way from looping through them all and probably not using dynamic SQL but can't figure out a way yet.
-
I had issues pasting my entire query so I just pasted the piece inside the loop. It works but seriously takes ways too long. I need to find a way from looping through them all and probably not using dynamic SQL but can't figure out a way yet.
I can probably help a bit once I can get other things off my mind. Yes, removing the looping is likely to help quite a bit.
You'll never get very far if all you do is follow instructions.
-
Where is the table? :confused: It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
Dear if you want to learn sql by online free tutorial. this tutorial may be helpful to you. www.javatpoint.com/sql-tutorial post your queries on javatpoint forum. www.javatpoint.com/forum
-
Dear if you want to learn sql by online free tutorial. this tutorial may be helpful to you. www.javatpoint.com/sql-tutorial post your queries on javatpoint forum. www.javatpoint.com/forum
Probably wouldn't help me, but maybe the OP.
You'll never get very far if all you do is follow instructions.