Insert 5 rows in one table based on row count of Another Table
-
Hello All:
I'm trying to set up some re-usable T-SQL for a task that I will likely use repeatedly in the future.
What I'm trying to do is insert/append five rows(conditions) for each model office in another table. I have 69 model office rows that I need base my insert off of. Each of these model offices gets 5 related conditions added to the condition table. The model office numbers are not consecutive. So I'm assumning this would make incrementing harder to do.
I would also like to fill a model office number field in each of the 5 conditions with the corresponding MO num.
Example:
MO Num: 482
CondNum MO Num
865 482
866 482
867 482
868 482
869 482
I'm assuming I should WHILE or BEGIN...END for the loop, but I'm not sure how to accomplish this. I've come up with a few ideas, but they just don't seem like the cleanest methods.
Thanks in advance for any help you can provide. Let me know if you have any questions.
Thanks,
Cleech
-
Your requirement statement is too vague to define a soulution
Please provide DDL and sample data including the "model office" table, the condition table and the "model office condition" table. Be sure to include all constraints including primary key, foreign key and uniqueness.
If you do not have primary key, foreign key and uniqueness constraints defined, please include them anyway.
See http://www.aspfaq.com/etiquette.asp?id=5006
SQL = Scarcely Qualifies as a Language
-
Try this:
-- Create tables ModelOffice, Condition, ConditionLine. ConditionLine table contains the office and Condition information.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ModelOffice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ModelOffice]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Condition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Condition]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConditionLine]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ConditionLine]
Create Table ModelOffice (
MONum INT Not NULL,
MOName Varchar(50) Not NULL
)
Create Table Condition (
CondNum INT Not Null,
CondDesc Varchar(255) Null
)
Create Table ConditionLine (
MONum INT Not NULL,
CondNum INT Not Null
)
-- Data population for 3 tables
Insert Into ModelOffice
Select 1, 'Office1' Union All
Select 2, 'Office2' Union All
Select 3, 'Office3' Union All
Select 4, 'Office4' Union All
Select 5, 'Office5' Union All
Select 6, 'Office6' Union All
Select 7, 'Office7'
Insert Into Condition
Select 1, 'Condition1' Union All
Select 2, 'Condition2' Union All
Select 3, 'Condition3' Union All
Select 4, 'Condition4' Union All
Select 5, 'Condition5' Union All
Select 6, 'Condition6' Union All
Select 7, 'Condition7' Union All
Select 8, 'Condition8' Union All
Select 9, 'Condition9' Union All
Select 10, 'Condition10' Union All
Select 11, 'Condition11' Union All
Select 12, 'Condition12' Union All
Select 13, 'Condition13' Union All
Select 14, 'Condition14' Union All
Select 15, 'Condition15' Union All
Select 16, 'Condition16' Union All
Select 17, 'Condition17' Union All
Select 18, 'Condition18' Union All
Select 19, 'Condition19'
-- Use cursor to populate office with condition information into the ConditionLine table
DECLARE @MOName varchar(50)
DECLARE @MONum INT
DECLARE @Finished Bit
-- To generate random number for data population purpose only
DECLARE @RangeStart INT
DECLARE @RangeEnd INT
-- Random seed
SET @RangeStart = 1
SET @RangeEnd = 19
DECLARE MO_Cursor CURSOR FOR
SELECT MONum, MOName
FROM ModelOffice
OPEN MO_Cursor
FETCH NEXT FROM MO_Cursor INTO @MONum, @MOName
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Insert condition for [' + @MOName + ']'
SET @Finished=0
WHILE @Finished = 0
Begin
Insert Into ConditionLine
SELECT top 5 @MONum, CAST(RAND(CAST(NEWID() AS VARBINARY))*(@RangeEnd-@RangeStart+1)+@RangeStart AS INT)
FROM dbo.SysObjects
IF (SELECT Top 1 Count(*) FROM ConditionLine WHERE MONum=@MONum GROUP BY CondNum HAVING COUNT(*)>1)>0
Delete ConditionLine
Where MONum=@MONum
ELSE
SET @Finished = 1
End
FETCH NEXT FROM MO_Cursor INTO @MONum, @MOName
END
CLOSE MO_Cursor
DEALLOCATE MO_Cursor
select * from ConditionLine
order by MONum, CondNum
-
You can use a CROSS JOIN to include all rows from both tables. For example:
Assuming the following:
1. Model office table: tblMO with column MOnum.
2. Condition table: tblCondition with column Condnum.
3. Result table: tblResult with columns MOnum, Condnum.
This would be the insert statement:
INSERT INTO [tblResult]
(MOnum, Condnum)
SELECT [tblMO].[MOnum], [tblCondition].[Condnum]
FROM [tblMO]
CROSS JOIN [tblCondition]
-
Thank you all for the great support so far!
Carl: I'm attaching the files you requested. Hopefully this clears it up a little.
Terry: Your code works great. Do you think I can change it handle 5 specific conditions. I know I should have been clearer in my original post.
I think the attached files will help clear things up.
Thanks again for all the help.
Model DDLCREATE TABLE [tblIPT_CTPmo] ([lngMOnum] [int] IDENTITY (1, 1) NOT NULL ,
[txtPolNum] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtTestPlan] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtPlanCode] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnFamilyBusiness] [bit] NULL ,
[txtJC] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtADbase] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmPolDate] [smalldatetime] NULL ,
[bytAgeBase] [tinyint] NULL ,
[dtmDOBbase] [smalldatetime] NULL ,
[txtSexBase] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curFaceAmt] [money] NULL ,
[txtWP] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtADB] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curADBface] [money] NULL ,
[txtMode] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtClassBase] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtRatingBase] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtUnderRatBase] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtSmokerBase] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtCIunits] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtSCIunits] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmSCIdob] [smalldatetime] NULL ,
[bytSCIage] [tinyint] NULL ,
[curFEamtBase] [money] NULL ,
[txtFEyrsBase] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtFEreasBase] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtDivOpt] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curCWA] [money] NULL ,
[txtCBarrgtNum] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtCBpayPoint] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtRider1] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curRiderFace1] [money] NULL ,
[txtRider2] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curRiderFace2] [money] NULL ,
[txtLBR] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentName1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentCode1] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentShare1] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentName2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentCode2] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentShare2] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentName3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentCode3] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtAgentShare3] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnAdvComm] [bit] NOT NULL ,
[memComments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnReview] [bit] NOT NULL ,
[txtNewBusSys] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmTimestamp] [datetime] NULL ,
[curStipPrem] [money] NULL ,
[ysnNAIC] [bit] NOT NULL CONSTRAINT [DF_tblIPT_CASTLmo_ysnNAIC] DEFAULT (0),
CONSTRAINT [PK_tblIPTmo] PRIMARY KEY CLUSTERED
(
[lngMOnum]
 
ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Condition DDLCREATE TABLE [tblConditions] ([lngCondNum] [int] IDENTITY (1, 1) NOT NULL ,
[txtTestPlanCond] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtProductCond] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lngMOnumCond] [int] NULL ,
[txtCycle] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtSystem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtFunction] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtProcess] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtTransaction] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmTransDate] [smalldatetime] NULL ,
[curTransAmt] [money] NULL ,
[strTransCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strTransType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strNewRateClass] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtMultiple] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtDBoptCond] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtRatingBaseCond] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtSmokerBaseCond] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bytAgeBaseCond] [tinyint] NULL ,
[txtSexBaseCond] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curFEamt1] [money] NULL ,
[txtFEyrs1] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curFEamt2] [money] NULL ,
[txtFEyrs2] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtNonFamMem] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[memCond] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[memExpResults] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtCondStatus] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtLog] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtReason] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtModifiedBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmModifiedDate] [smalldatetime] NULL ,
[txtProdPolNumCond] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnReviewCond] [bit] NULL ,
[txtJCCond] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtDataType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[memInput] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtUnappDesc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtTestingType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtChangeDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtTestPhase] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txtUserCond] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmTimestampCond] [datetime] NULL ,
[txtPlanCodeCond] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnRegression] [bit] NULL ,
CONSTRAINT [PK_tblConditions_1] PRIMARY KEY CLUSTERED
(
[lngCondNum]
 
ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Model Inserts--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[tblIPT_CTPmo] ON
GO
PRINT 'Inserting values into [tblIPT_CTPmo]'
INSERT INTO [tblIPT_CTPmo] VALUES(955,'49204347','Term 20YearTerm/IPT/IPTR','Alabama','IPT',NULL,'AL','107','Jan 8 2007 12:00:00:000AM',18,'Jan 8 1989 12:00:00:000AM','M',535000.0000,NULL,NULL,NULL,'A','9','N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,INSERT INTO [tblIPT_CTPmo] VALUES(956,NULL,'Term 20YearTerm/IPT/IPTR','Montana','IPT',NULL,'MT','107','Jan 8 2007 12:00:00:000AM',33,'Jun 2 1974 12:00:00:000AM','F',500000.0000,NULL,NULL,NULL,'S','4','S',NULL,'Y','10',NULL,NULL,NULL,NULL,NULL,NULL,'5',65
INSERT INTO [tblIPT_CTPmo] VALUES(957,'49204446','Term 20YearTerm/IPT/IPTR','Azizona','IPT',NULL,'AZ','107','Jan 8 2007 12:00:00:000AM',59,'Jan 17 1948 12:00:00:000AM','M',600000.0000,NULL,NULL,NULL,'A',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(958,'49204810','Term 20YearTerm/IPT/IPTR','Colorado','IPT',NULL,'CO','107','Jan 8 2007 12:00:00:000AM',31,'Mar 13 1976 12:00:00:000AM','F',600000.0000,'3','2',300000.0000,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(959,'49204816','Term 20YearTerm/IPT/IPTR','District of Columbia','IPT',NULL,'DC','107','Jan 8 2007 12:00:00:000AM',18,'Jan 8 1989 12:00:00:000AM','M',250000.0000,NULL,NULL,NULL,'N',NULL,'Z',NULL,'Y',NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(960,'49204447','Term 20YearTerm/IPT/IPTR','Delaware','IPT',NULL,'DE','107','Jan 8 2007 12:00:00:000AM',45,'Feb 15 1962 12:00:00:000AM','M',275000.0000,'1','1',200000.0000,'M','3','Z','116','N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(961,'49204385','Term 20YearTerm/IPT/IPTR','Idaho','IPT',NULL,'ID','107','Jan 8 2007 12:00:00:000AM',19,'Jul 2 1988 12:00:00:000AM','F',250000.0000,NULL,NULL,NULL,'A','8','N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'5
INSERT INTO [tblIPT_CTPmo] VALUES(962,'49204387','Term 20YearTerm/IPT/IPTR','Indiana','IPT',NULL,'IN','107','Jan 8 2007 12:00:00:000AM',29,'Mar 12 1978 12:00:00:000AM','M',483000.0000,'1','1',300000.0000,'S',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(963,'49204388','Term 20YearTerm/IPT/IPTR','Pennsylvania','IPT',NULL,'PA','107','Jan 8 2007 12:00:00:000AM',60,'May 22 1947 12:00:00:000AM','M',888000.0000,NULL,NULL,NULL,'M','7','N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(964,NULL,'Term 20YearTerm/IPT/IPTR','Kansas','IPT',NULL,'KS','107','Jan 8 2007 12:00:00:000AM',33,'May 7 1974 12:00:00:000AM','M',550000.0000,'2','1',300000.0000,'S','2','N',NULL,'N','5',NULL,NULL,NULL,NULL,NULL,NULL,'1'
INSERT INTO [tblIPT_CTPmo] VALUES(965,'49204389','Term 20YearTerm/IPT/IPTR','Maine','IPT',NULL,'ME','107','Jan 8 2007 12:00:00:000AM',24,'Apr 22 1983 12:00:00:000AM','M',775000.0000,'1','1',250000.0000,'S',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(966,'49204390','Term 20YearTerm/IPT/IPTR','Michigan','IPT',NULL,'MI','107','Jan 8 2007 12:00:00:000AM',49,'Apr 13 1958 12:00:00:000AM','F',375000.0000,'1','1',300000.0000,'Q',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(967,'49204391','Term 20YearTerm/IPT/IPTR','Massachusetts','IPT',NULL,'MA','107','Jan 8 2007 12:00:00:000AM',55,'Apr 2 1952 12:00:00:000AM','F',634000.0000,NULL,NULL,NULL,'M',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(968,'49204920','Term 20YearTerm/IPT/IPTR','Mississippi','IPT',NULL,'MS','107','Jan 8 2007 12:00:00:000AM',20,'Apr 6 1987 12:00:00:000AM','M',251000.0000,'1','1',250000.0000,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(969,'49204494','Term 20YearTerm/IPT/IPTR','Missouri','IPT',NULL,'MO','107','Jan 8 2007 12:00:00:000AM',41,'Feb 14 1966 12:00:00:000AM','F',555000.0000,NULL,'1',300000.0000,'Q',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(970,'49204349','Term 20YearTerm/IPT/IPTR','Nevada','IPT',NULL,'NV','107','Jan 8 2007 12:00:00:000AM',61,'May 13 1946 12:00:00:000AM','M',783000.0000,NULL,NULL,NULL,'Q',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(971,'49204821','Term 20YearTerm/IPT/IPTR','New Hamsphire','IPT',NULL,'NH','107','Jan 8 2007 12:00:00:000AM',33,'Mar 25 1974 12:00:00:000AM','M',350000.0000,NULL,NULL,NULL,'A','5','S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(972,'49204823','Term 20YearTerm/IPT/IPTR','New Mexico','IPT',NULL,'NM','107','Jan 8 2007 12:00:00:000AM',65,'Apr 20 1942 12:00:00:000AM','F',250000.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(973,'49204351','Term 20YearTerm/IPT/IPTR','North Dakota','IPT',NULL,'ND','107','Jan 8 2007 12:00:00:000AM',36,'Apr 17 1971 12:00:00:000AM','M',700000.0000,NULL,NULL,NULL,'A',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(974,'49204392','Term 20YearTerm/IPT/IPTR','Ohio','IPT',NULL,'OH','107','Jan 8 2007 12:00:00:000AM',27,'May 24 1980 12:00:00:000AM','F',655000.0000,'1','1',300000.0000,'A',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(975,'49204394','Term 20YearTerm/IPT/IPTR','South Dakota','IPT',NULL,'SD','107','Jan 8 2007 12:00:00:000AM',23,'Feb 24 1984 12:00:00:000AM','F',590400.0000,NULL,'1',300000.0000,'S','2','Z','116','N',NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(976,'49204352','Term 20YearTerm/IPT/IPTR','Hawaii','IPT',NULL,'HI','107','Jan 8 2007 12:00:00:000AM',55,'Jan 18 1952 12:00:00:000AM','M',10000000.0000,NULL,NULL,NULL,'A',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(977,'49204825','Term 20YearTerm/IPT/IPTR','Utah','IPT',NULL,'UT','107','Jan 8 2007 12:00:00:000AM',25,'Jun 3 1982 12:00:00:000AM','M',900000.0000,NULL,'3',250000.0000,'C',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(978,'49204396','Term 20YearTerm/IPT/IPTR','Virgina','IPT',NULL,'VA','107','Jan 8 2007 12:00:00:000AM',43,'Feb 23 1964 12:00:00:000AM','F',500000.0000,NULL,NULL,NULL,'A','12','N',NULL,'N',NULL,NULL,NULL,NULL,4.1000,'4','M'
INSERT INTO [tblIPT_CTPmo] VALUES(979,'49204826','Term 20YearTerm/IPT/IPTR','Washington','IPT',NULL,'WA','107','Jan 8 2007 12:00:00:000AM',48,'Feb 9 1959 12:00:00:000AM','F',917000.0000,NULL,NULL,NULL,'Q','11','S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(980,'49204399','Term 20YearTerm/IPT/IPTR','West Virgina','IPT',NULL,'WV','107','Jan 8 2007 12:00:00:000AM',59,'May 4 1948 12:00:00:000AM','F',400000.0000,'1',NULL,NULL,'M','3','Z','116','N',NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(981,'49204454','Term 20YearTerm/IPT/IPTR','Oklahoma','IPT',NULL,'OK','107','Jan 8 2007 12:00:00:000AM',27,'Mar 12 1980 12:00:00:000AM','F',8350000.0000,NULL,NULL,NULL,'C',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(982,'49204922','Term 20YearTerm/IPT/IPTR','Montana','IPTU',NULL,'MT','107','Jan 8 2007 12:00:00:000AM',15,'Jun 3 1992 12:00:00:000AM','M',950000.0000,'1','1',250000.0000,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(983,'49204365','Term 20YearTerm/IPT/IPTR','California','IPT',1,'CA','107','Jan 8 2007 12:00:00:000AM',35,'Jun 23 1972 12:00:00:000AM','M',750000.0000,NULL,NULL,NULL,'Q','7','S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(984,'49204829','Term 20YearTerm/IPT/IPTR','Arkansas','IPT',NULL,'AR','107','Jan 8 2007 12:00:00:000AM',46,'Feb 20 1961 12:00:00:000AM','F',250000.0000,NULL,NULL,NULL,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(985,'49204501','Term 20YearTerm/IPT/IPTR','Connecticut','IPT',0,'CT','107','Jan 8 2007 12:00:00:000AM',33,'Jan 11 1974 12:00:00:000AM','F',500000.0000,NULL,NULL,NULL,'S',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(986,NULL,'Term 20YearTerm/IPT/IPTR','Florida','IPT',NULL,'FL','107','Jan 8 2007 12:00:00:000AM',42,'Jan 25 1965 12:00:00:000AM','M',600000.0000,NULL,NULL,NULL,'Q','10','S',NULL,'Y','10',NULL,NULL,NULL,NULL,NULL,NULL,'1',1
INSERT INTO [tblIPT_CTPmo] VALUES(987,'49204458','Term 20YearTerm/IPT/IPTR','Illinois','IPT',NULL,'IL','107','Jan 8 2007 12:00:00:000AM',57,'May 25 1950 12:00:00:000AM','F',500000.0000,NULL,NULL,NULL,'Q',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(988,'49204831','Term 20YearTerm/IPT/IPTR','New Jersey','IPT',NULL,'NJ','107','Jan 8 2007 12:00:00:000AM',60,'Mar 5 1947 12:00:00:000AM','M',450000.0000,NULL,NULL,NULL,'S',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(989,'49204459','Term 20YearTerm/IPT/IPTR','Maryland','IPT',NULL,'MD','107','Jan 8 2007 12:00:00:000AM',30,'May 26 1977 12:00:00:000AM','M',999999.0000,'1','1',300000.0000,'S',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(990,'49204904','Term 20YearTerm/IPT/IPTR','New York','IPT',NULL,'NY','107','Jan 8 2007 12:00:00:000AM',15,'Mar 2 1992 12:00:00:000AM','M',250000.0000,NULL,NULL,NULL,'C',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(991,'49204923','Term 20YearTerm/IPT/IPTR','Texas','IPT',1,'TX','107','Jan 8 2007 12:00:00:000AM',20,'Mar 4 1987 12:00:00:000AM','M',250000.0000,'1','1',250000.0000,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(992,'49204460','Term 20YearTerm/IPT/IPTR','Rhode Island','IPT',NULL,'RI','107','Jan 8 2007 12:00:00:000AM',49,'Mar 14 1958 12:00:00:000AM','F',250000.0000,'1','1',250000.0000,'Q',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(996,'49204859','Term 20YearTerm/IPT/IPTR','Wisconsin','IPT',NULL,'WI','107','Jan 8 2007 12:00:00:000AM',65,'Jun 6 1942 12:00:00:000AM','F',250000.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(997,'49204861','Term 20YearTerm/IPT/IPTR','North Carolina','IPT',NULL,'NC','107','Jan 8 2007 12:00:00:000AM',65,'Jan 11 1942 12:00:00:000AM','F',899999.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(998,'49204863','Term 20YearTerm/IPT/IPTR','Minnesota','IPT',NULL,'MN','107','Jan 8 2007 12:00:00:000AM',65,'Feb 2 1942 12:00:00:000AM','F',725000.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(999,'49204865','Term 20YearTerm/IPT/IPTR','Iowa','IPT',NULL,'IA','107','Jan 8 2007 12:00:00:000AM',65,'May 28 1942 12:00:00:000AM','M',1000000.0000,NULL,NULL,NULL,'Q','13','S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'
INSERT INTO [tblIPT_CTPmo] VALUES(1000,'49204538','Term 20YearTerm/IPT/IPTR','Georgia','IPT',NULL,'GA','107','Jan 8 2007 12:00:00:000AM',36,'May 17 1971 12:00:00:000AM','M',450000.0000,NULL,NULL,NULL,'A',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1001,'49204467','Term 20YearTerm/IPT/IPTR','Kentucky','IPT',NULL,'KY','107','Jan 8 2007 12:00:00:000AM',45,'Jan 21 1962 12:00:00:000AM','M',569000.0000,'1','1',300000.0000,'M','4','Z','116','N',NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(1002,'49204407','Term 20YearTerm/IPT/IPTR','Louisiana','IPT',NULL,'LA','107','Jan 8 2007 12:00:00:000AM',29,'Jan 8 1978 12:00:00:000AM','M',1500000.0000,'1','1',300000.0000,'S',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(1003,'49204868','Term 20YearTerm/IPT/IPTR','Nebraska','IPT',NULL,'NE','107','Jan 8 2007 12:00:00:000AM',27,'Jan 7 1980 12:00:00:000AM','F',826500.0000,'1','1',300000.0000,'N',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(1004,'49204925','Term 20YearTerm/IPT/IPTR','Oregon','IPT',NULL,'OR','107','Jan 8 2007 12:00:00:000AM',49,'Jun 20 1958 12:00:00:000AM','F',2000000.0000,'1','1',300000.0000,'Q',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1005,'49204878','Term 20YearTerm/IPT/IPTR','Wyoming','IPT',NULL,'WY','106','Dec 23 2006 12:00:00:000AM',59,'Feb 23 1948 12:00:00:000AM','M',5000000.0000,NULL,NULL,NULL,'A',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(1006,'49204419','Term 20YearTerm/IPT/IPTR','Vermont','IPT',NULL,'VT','107','Jan 8 2007 12:00:00:000AM',55,'Jan 24 1952 12:00:00:000AM','F',639000.0000,NULL,NULL,NULL,'M',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1007,'49204425','Term 20YearTerm/IPT/IPTR','Tennessee','IPT',NULL,'TN','107','Jan 8 2007 12:00:00:000AM',41,'Apr 7 1966 12:00:00:000AM','F',5555000.0000,NULL,NULL,NULL,'Q',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(1008,'49204927','Term 20YearTerm/IPT/IPTR','South Carolina','IPT',1,'SC','107','Jan 8 2007 12:00:00:000AM',23,'May 5 1984 12:00:00:000AM','F',635400.0000,NULL,'1',300000.0000,'S',NULL,'Z',NULL,'Y',NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1009,'49204433','Term 20YearTerm/IPT/IPTR','New York','IPT',NULL,'NY','107','Jan 8 2007 12:00:00:000AM',50,'Jun 5 1957 12:00:00:000AM','M',717000.0000,NULL,NULL,NULL,'M',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(1010,'49204435','Term 20YearTerm/IPT/IPTR','New Jersey','IPT',NULL,'NJ','107','Jan 8 2007 12:00:00:000AM',59,'Mar 14 1948 12:00:00:000AM','F',827300.0000,NULL,NULL,NULL,'C','6','N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(1011,'49204872','Term 20YearTerm/IPT/IPTR','New York','IPT',NULL,'NY','107','Jan 8 2007 12:00:00:000AM',55,'May 17 1952 12:00:00:000AM','F',900000.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL,NU
INSERT INTO [tblIPT_CTPmo] VALUES(1012,'49204874','Term 20YearTerm/IPT/IPTR','New Jersey','IPT',NULL,'NJ','107','Jan 8 2007 12:00:00:000AM',65,'Feb 8 1942 12:00:00:000AM','F',9999999.0000,NULL,NULL,NULL,'Q',NULL,'S',NULL,'Y',NULL,NULL,NULL,NULL,NULL,NULL
INSERT INTO [tblIPT_CTPmo] VALUES(1013,'49204440','Term 20YearTerm/IPT/IPTR','New York','IPT',NULL,'NY','107','Jan 8 2007 12:00:00:000AM',65,'Mar 18 1942 12:00:00:000AM','M',4999999.0000,NULL,NULL,NULL,'S',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,N
INSERT INTO [tblIPT_CTPmo] VALUES(1014,NULL,'Term 20YearTerm/IPT/IPTR','Alaska','IPT',NULL,'AK','107','Jan 8 2007 12:00:00:000AM',33,'Feb 5 1974 12:00:00:000AM','F',500000.0000,NULL,NULL,NULL,'S',NULL,'S',NULL,'Y','10',NULL,NULL,NULL,NULL,NULL,NULL,'1',3
INSERT INTO [tblIPT_CTPmo] VALUES(1015,NULL,'Term 20YearTerm/IPT/IPTR','Alaska','IPT',NULL,'AK','107','Jan 8 2007 12:00:00:000AM',33,'May 23 1974 12:00:00:000AM','F',517000.0000,'1','1',300000.0000,'S',NULL,'S',NULL,'Y','10',NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(1016,'49204441','Term 20YearTerm/IPT/IPTR','Virgina','IPT',NULL,'VA','107','Jan 8 2007 12:00:00:000AM',43,'Jun 12 1964 12:00:00:000AM','F',379000.0000,NULL,NULL,NULL,'A',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,4.1000,'4','M
INSERT INTO [tblIPT_CTPmo] VALUES(1017,'49204442','Term 20YearTerm/IPT/IPTR','Texas','IPT',NULL,'TX','107','Jan 8 2007 12:00:00:000AM',20,'Feb 12 1987 12:00:00:000AM','M',250000.0000,'1','1',250000.0000,'A',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,
INSERT INTO [tblIPT_CTPmo] VALUES(1018,'49204443','Term 20YearTerm/IPT/IPTR','Iowa','IPT',NULL,'IA','107','Jan 8 2007 12:00:00:000AM',65,'Jun 14 1942 12:00:00:000AM','M',581000.0000,NULL,NULL,NULL,'Q',NULL,'N',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'
INSERT INTO [tblIPT_CTPmo] VALUES(1039,'49204492','Term 20YearTerm/IPT/IPTR','california','IPT',NULL,'CA','107','Jan 8 2007 12:00:00:000AM',65,'Jun 14 1942 12:00:00:000AM','M',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1040,'49204497','Term 20YearTerm/IPT/IPTR','california','IPT',NULL,'CA','107','Jan 8 2007 12:00:00:000AM',65,'Jun 14 1942 12:00:00:000AM','F',10000000.0000,NULL,NULL,NULL,'A',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1041,'49204499','Term 20YearTerm/IPT/IPTR','california','IPT',NULL,'CA','107','Jan 8 2007 12:00:00:000AM',58,'Apr 19 1949 12:00:00:000AM','F',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1042,'49204543','Term 20YearTerm/IPT/IPTR','california','IPT',NULL,'CA','107','Jan 8 2007 12:00:00:000AM',59,'Jun 21 1948 12:00:00:000AM','M',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1043,'49204478','Term 20YearTerm/IPT/IPTR','Texas','IPT',NULL,'TX','107','Jan 8 2007 12:00:00:000AM',65,'Jun 14 1942 12:00:00:000AM','M',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1044,'49204482','Term 20YearTerm/IPT/IPTR','Texas','IPT',NULL,'TX','107','Jan 8 2007 12:00:00:000AM',65,'Jun 14 1942 12:00:00:000AM','F',10000000.0000,NULL,NULL,NULL,'A',NULL,'V',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1045,'49204483','Term 20YearTerm/IPT/IPTR','Texas','IPT',NULL,'TX','107','Jan 8 2007 12:00:00:000AM',57,'Mar 28 1950 12:00:00:000AM','F',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
INSERT INTO [tblIPT_CTPmo] VALUES(1046,'49204484','Term 20YearTerm/IPT/IPTR','Texas','IPT',NULL,'TX','107','Jan 8 2007 12:00:00:000AM',60,'Mar 8 1947 12:00:00:000AM','M',10000000.0000,NULL,NULL,NULL,'A',NULL,'P',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NUL
PRINT 'Done'SET IDENTITY_INSERT [dbo].[tblIPT_CTPmo] OFF
GO
SET NOCOUNT OFF
Condition Inserts--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[tblConditions] ON
GO
PRINT 'Inserting values into [tblConditions]'
INSERT INTO [tblConditions] VALUES(158002,'Term 20YearTerm/IPT/IPTR','IPT',955,'2','CTP','Remittance','Remittance','KA -- First Payment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Process a First Payment on AD 107 IPTINSERT INTO [tblConditions] VALUES(158003,'Term 20YearTerm/IPT/IPTR','IPT',955,'2','CTP','Remittance','Remittance','KA -- First Payment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Process a First Payment on an AD 107
INSERT INTO [tblConditions] VALUES(158004,'Term 20YearTerm/IPT/IPTR','IPT',955,'2','CTP','Remittance','Remittance','KA -- First Payment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Process a First Payment on an AD 107 I
INSERT INTO [tblConditions] VALUES(158005,'Term 20YearTerm/IPT/IPTR','IPT',955,'2','CTP','Remittance','Remittance','KA -- First Payment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Process a First Payment on an AD 107 I
INSERT INTO [tblConditions] VALUES(158008,'Term 20YearTerm/IPT/IPTR','IPT',955,'2','CTP','Remittance','Remittance','KA -- First Payment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Process a First Payment on an AD 107 I
PRINT 'Done'SET IDENTITY_INSERT [dbo].[tblConditions] OFF
GO
SET NOCOUNT OFF
ResultslngCondNum txtTestPlanCond txtProductCond lngMOnumCond txtCycle 158002 Term 20YearTerm/IPT/IPTR IPT 955 2 158003 Term 20YearTerm/IPT/IPTR IPT 955 2 158004 Term 20YearTerm/IPT/IPTR IPT 955 2 158005 Term 20YearTerm/IPT/IPTR IPT 955 2 158008 Term 20YearTerm/IPT/IPTR IPT 955 2 158009 Term 20YearTerm/IPT/IPTR IPT 956 2 158010 Term 20YearTerm/IPT/IPTR IPT 956 2 158011 Term 20YearTerm/IPT/IPTR IPT 956 2 158012 Term 20YearTerm/IPT/IPTR IPT 956 2 158013 Term 20YearTerm/IPT/IPTR IPT 956 2 158014 Term 20YearTerm/IPT/IPTR IPT 957 2 158015 Term 20YearTerm/IPT/IPTR IPT 957 2 158016 Term 20YearTerm/IPT/IPTR IPT 957 2 158017 Term 20YearTerm/IPT/IPTR IPT 957 2 158018 Term 20YearTerm/IPT/IPTR IPT 957 2 158019 Term 20YearTerm/IPT/IPTR IPT 958 2 158020 Term 20YearTerm/IPT/IPTR IPT 958 2 158021 Term 20YearTerm/IPT/IPTR IPT 958 2 158022 Term 20YearTerm/IPT/IPTR IPT 958 2 158023 Term 20YearTerm/IPT/IPTR IPT 958 2 158024 Term 20YearTerm/IPT/IPTR IPT 959 2 158025 Term 20YearTerm/IPT/IPTR IPT 959 2 158026 Term 20YearTerm/IPT/IPTR IPT 959 2 158027 Term 20YearTerm/IPT/IPTR IPT 959 2 158028 Term 20YearTerm/IPT/IPTR IPT 959 2 -
What I showed you was to prove the data model was working for you. Specially the ConditionLine table is the key in my model. I put this table because the relationship between ModelOffice and Condition seemed to be Many-to-Many. I found that in your model the relationship between ModelOffice and Condition is 1-to-Many. What if some condition can be applied to many fifferent Model Offices? Anyway you are the best person who know s your business.
And the WHILE loop in my script is fake meaning that random number generator was used to generate condition information. In reality, you may have to use DTS Import functionality after you generate Excel Spreadsheet which contains two columns, say lngMOnum and lngCondNum in your case.
I changed the ConditionLine table to your naming convention as follows:
Create Table tblConditionLine (
lngMOnum INT Not NULL,
lngCondNum INT Not Null
)
Once DTS Import is completed, use the following to test the result.
SELECT tblIPT_CTPmo.*, tblConditions.* FROM tblIPT_CTPmo JOIN tblConditionLine ON (tblIPT_CTPmo.lngMOnum = tblConditionLine.lngMOnum) JOIN tblConditions ON (tblConditionLine.lngCondNum = tblConditions.lngCondNum) WHERE tblIPT_CTPmo.lngMOnum IN (955,956,957,958) -- sample offices
Advise if you need further discussion.
-
Hello All:
Thanks for all the help so far. I just wanted to share what I ended up with with everyone. The first block of code is my solution. Please let me know of anything that can be improved or done differently. Right now I know that it works, but I'm not sure if it is the best approach.
The second block of code is my new problem. I'm trying to set up a stored procedure for the code in the first block. I'm having trouble passing variables. In particular I'm trying to pass a table name variable into the select statement of a cursor declaration. Can this be done? I'm not sure if it is just a syntax error or something else.
Please take a look and let me know what can be done.
Thanks in advance.
Working Code:
--Declare Variables
DECLARE @moCount as INT, @moNumber as INT, @CondNumber as INT, @cntr as INT, @high as INT, @low as INT, @condTotal as INT
DECLARE @testPlan as NVARCHAR(50), @product as NVARCHAR(50)
--Assign Initial values
SET @testPlan = 'Term 20YearTerm/IPT/IPTR'
SET @moCount = (select count(*) from tblIPT_CTPmo where txtTestPlan = @testPlan )+1
SET @cntr = 1
WHILE @cntr != @moCount--Begin While Loop to Insert 5 conditions for each model office
BEGIN
INSERT INTO Condition_Attempt
(txtTestPlanCond, txtProductCond, lngMOnumCond, txtCycle, txtSystem, txtFunction, txtProcess,
txtTransaction, dtmTransDate, curTransAmt,strTransCode, strTransType, strNewRateClass, txtMultiple,
txtDBoptCond, txtRatingBaseCond, txtSmokerBaseCond, bytAgeBaseCond, txtSexBaseCond, curFEamt1,
txtFEyrs1, curFEamt2, txtFEyrs2, txtNonFamMem, memCond,memExpResults, txtCondStatus, txtLog,
txtReason, txtModifiedBy, dtmModifiedDate, txtProdPolNumCond, ysnReviewCond, txtJCCond, txtDataType,
memInput, txtUnappDesc, txtTestingType, txtChangeDesc, txtTestPhase, txtUserCond, dtmTimestampCond,
txtPlanCodeCond, ysnRegression)
SELECT
txtTestPlanCond, txtProductCond, lngMOnumCond, txtCycle, txtSystem, txtFunction, txtProcess,
txtTransaction, dtmTransDate, curTransAmt, strTransCode, strTransType, strNewRateClass, txtMultiple,
txtDBoptCond, txtRatingBaseCond,txtSmokerBaseCond, bytAgeBaseCond, txtSexBaseCond, curFEamt1, txtFEyrs1,
curFEamt2, txtFEyrs2, txtNonFamMem, memCond,memExpResults, txtCondStatus, txtLog, txtReason, txtModifiedBy, dtmModifiedDate, txtProdPolNumCond, ysnReviewCond, txtJCCond, txtDataType, memInput, txtUnappDesc, txtTestingType, txtChangeDesc, txtTestPhase, txtUserCond,dtmTimestampCond, txtPlanCodeCond, ysnRegression
FROM Condition_Attempt
WHERE (lngCondNum = 209297) OR (lngCondNum = 209298) OR (lngCondNum = 209299) OR (lngCondNum = 209300) OR
(lngCondNum = 209301)
SET @cntr = @cntr + 1
END--End While Loop for Condition insert-
SET @cntr = 1 -- Counter Back to 1
----------------------------------------------------------------------------Use to update specific fields in Condition Table
--------------------------------------------------------------------------/*UPDATE Condition_Attempt SET txtProductCond = N'[IPT]' WHERE (lngCondNum BETWEEN @high AND @low)
WHILE @cntr < @moCount
BEGIN
SELECT lngMOnum from tblIPT_CTPmo where txtTestPlan = @testPlan ORDER BY lngMOnum ASC
PRINT @cntr
SET @cntr = @cntr + 1
END
*/
--------------------------------------------------------------------------SET @high = (SELECT TOP 1 lngCondNum FROM Condition_Attempt ORDER BY lngCondNum DESC)
SET @low = (@high - (@mocount-1)*5)+1
----------------------------------------------------------------------------Begin mapping/update insert MO number 5 times for each MO number in Test Plan
--Iterate through the model office using cursors and update every 5 conditions with 1 MO Number
--------------------------------------------------------------------------DECLARE mo_curs CURSOR FOR
SELECT lngMOnum from tblIPT_CTPmo where txtTestPlan = @testPlan
OPEN mo_curs
SET @CondNumber = @low
FETCH NEXT FROM mo_curs INTO @moNumber
WHILE (@@FETCH_STATUS=0 )
BEGIN
WHILE @CondNumber <> @high
BEGIN
WHILE @cntr < 6
BEGIN
EXEC ('UPDATE Condition_Attempt SET lngMOnumCond = ' + @moNumber + ' WHERE(lngCondNum = ' + @CondNumber + ')' )
SET @cntr = @cntr + 1
SET @CondNumber =@CondNumber + 1
END
SET @cntr = 1
BREAK
END
FETCH NEXT FROM mo_curs INTO @moNumber
END
CLOSE mo_curs
DEALLOCATE mo_curs
Stored Procedure Problem:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_Loopty
(
@TPlan NVARCHAR(50),
@Prod NVARCHAR(50),
@MOtable NVARCHAR(50)
--Needed variables: Condition Numbers, Table Name?,
)
AS
--Declare Variables
DECLARE @moCount as INT, @moNumber as INT, @CondNumber as INT, @cntr as INT, @high as INT, @low as INT, @condTotal as INT
DECLARE @testPlan as NVARCHAR(50), @product as NVARCHAR(50), @tableMO AS NVARCHAR(50)
--Assign Initial values
SET @tableMO = tblIPT_CTPmo --@MOtable
SET @testPlan = @Tplan
SET @cntr = 1
SET @moCount = ('select count(*) from' + @tableMO + ' where txtTestPlan = ' + @testPlan )+1
WHILE @cntr != @moCount--Begin While Loop to Insert 5 conditions for each model office
BEGIN
INSERT INTO Condition_Attempt
(txtTestPlanCond, txtProductCond, lngMOnumCond, txtCycle, txtSystem, txtFunction, txtProcess,
txtTransaction, dtmTransDate, curTransAmt,strTransCode, strTransType, strNewRateClass, txtMultiple,
txtDBoptCond, txtRatingBaseCond, txtSmokerBaseCond, bytAgeBaseCond, txtSexBaseCond, curFEamt1,
txtFEyrs1, curFEamt2, txtFEyrs2, txtNonFamMem, memCond,memExpResults, txtCondStatus, txtLog,
txtReason, txtModifiedBy, dtmModifiedDate, txtProdPolNumCond, ysnReviewCond, txtJCCond, txtDataType,
memInput, txtUnappDesc, txtTestingType, txtChangeDesc, txtTestPhase, txtUserCond, dtmTimestampCond,
txtPlanCodeCond, ysnRegression)
SELECT
txtTestPlanCond, txtProductCond, lngMOnumCond, txtCycle, txtSystem, txtFunction, txtProcess,
txtTransaction, dtmTransDate, curTransAmt, strTransCode, strTransType, strNewRateClass, txtMultiple,
txtDBoptCond, txtRatingBaseCond,txtSmokerBaseCond, bytAgeBaseCond, txtSexBaseCond, curFEamt1, txtFEyrs1,
curFEamt2, txtFEyrs2, txtNonFamMem, memCond,memExpResults, txtCondStatus, txtLog, txtReason, txtModifiedBy, dtmModifiedDate, txtProdPolNumCond, ysnReviewCond, txtJCCond, txtDataType, memInput, txtUnappDesc, txtTestingType, txtChangeDesc, txtTestPhase, txtUserCond,dtmTimestampCond, txtPlanCodeCond, ysnRegression
FROM Condition_Attempt
WHERE (lngCondNum = 209297) OR (lngCondNum = 209298) OR (lngCondNum = 209299) OR (lngCondNum = 209300) OR
(lngCondNum = 209301)
SET @cntr = @cntr + 1
END--End While Loop for Condition insert-
SET @cntr = 1 -- Counter Back to 1
----------------------------------------------------------------------------Use to update specific fields in Condition Table
--------------------------------------------------------------------------/*UPDATE Condition_Attempt SET txtProductCond = N'[IPT]' WHERE (lngCondNum BETWEEN @high AND @low)
WHILE @cntr < @moCount
BEGIN
SELECT lngMOnum from tblIPT_CTPmo where txtTestPlan = @testPlan ORDER BY lngMOnum ASC
PRINT @cntr
SET @cntr = @cntr + 1
END
*/
--------------------------------------------------------------------------SET @high = (SELECT TOP 1 lngCondNum FROM Condition_Attempt ORDER BY lngCondNum DESC)
SET @low = (@high - (@mocount-1)*5)+1
----------------------------------------------------------------------------Begin mapping/update insert MO number 5 times for each MO number in Test Plan
--Iterate through the model office using cursors and update every 5 conditions with 1 MO Number
--------------------------------------------------------------------------DECLARE mo_curs CURSOR FOR
('SELECT lngMOnum from ' + @tableMO + ' where txtTestPlan = '+ @testPlan)
OPEN mo_curs
SET @CondNumber = @low
FETCH NEXT FROM mo_curs INTO @moNumber
WHILE (@@FETCH_STATUS=0 )
BEGIN
WHILE @CondNumber <> @high
BEGIN
WHILE @cntr < 6
BEGIN
EXEC ('UPDATE Condition_Attempt SET lngMOnumCond = ' + @moNumber + ' WHERE(lngCondNum = ' + @CondNumber + ')' )
SET @cntr = @cntr + 1
SET @CondNumber =@CondNumber + 1
END
SET @cntr = 1
BREAK
END
FETCH NEXT FROM mo_curs INTO @moNumber
END
CLOSE mo_curs
DEALLOCATE mo_curs
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
It can be done, but the whole code must be dynamic (from the declare cursor to the deallocate).
Also without looking at the code I'm sure there must be a better way to do this. Why do you think you need a cursor with this problem... And what is the problem you are trying to solve?
-
Using cursors was suggested as a solution and it seems to fit pretty well. At least the code is working as I expect it to. I will try making the whole code dynamic as you suggested.
Here is the problem from my original post:
- I'm trying to set up some re-usable T-SQL for a task that I will likely use repeatedly in the future.
- What I'm trying to do is insert/append five rows(conditions) for each model office in another table. I have 69 model office rows that I need base my insert off of. Each of these model offices gets 5 related conditions added to the condition table. The model office numbers are not consecutive. So I'm assumning this would make incrementing harder to do.
- I would also like to fill a model office number field in each of the 5 conditions with the corresponding MO num.
- Example:
- MO Num: 482
- CondNum MO Num
- 865 482
- 866 482
- 867 482
- 868 482
- 869 482
- I'm assuming I should WHILE or BEGIN...END for the loop, but I'm not sure how to accomplish this. I've come up with a few ideas, but they just don't seem like the cleanest methods.
-
That's pretty easy (unless I don't have all the requirements for the problem).
All you need is the have this data in a derived table select :
Id1, id2, RowsNum
then you do a triangular join on a numbers table (tally table) and your done.
Need code sample for this?
-
If you have a code sample for this, that would be great.
-
Soryr for the incomplete answer but that should be enough to get you started.
I'm assuming here that you have a numbers table set up (with the numbers from 1 to 11000).
Select id1, id2, RowNums from dbo.BaseTable BT
inner join dbo.Numbers N on BT.RowNums >= N.Numbr
This will multiple the ids n times dynamically (RowNums times actually).
Message back if you have troubles with it and I'll post a full answer tomorrow.
Good luck.
-
If you don't mind, could you send me a sample. I think I'm following you, but an example would help.
Thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply