Can one use a Cursor in an UDF

Question

Can one use a Cursor in an UDF

Mark Giles

SSC-Addicted

Points: 435
More actions
April 8, 2004 at 9:52 am

#107754

i'm new to this forum and to UDFs. i have a need to iterate through a recordset. i'm able to do that with a cursor in store procedures, but i was wondering if that is possible in a UDF?
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Mike Good SSCertifiable Points: 7415 More actions · Answer 1

I have never wanted to do this before, but now curious. Just made both a scalar and table-valued function that declares, opens, traverses, and deallcoates a simple cursor. Both worked fine. I'm sure there are lot of things you can't put in the cursor declareation (same list of things you can't put in UDF).

Mark Giles SSC-Addicted Points: 435 More actions · Answer 2

Mark Giles

SSC-Addicted

Points: 435

April 9, 2004 at 6:36 am

#502276

Thanks Mike.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

You do know of course....that using cursors is one fast way to ensure your "financial controller" get's least value out of his/her spend on the SQL Server.

95% of things that are done usings cursors can be done far faster without.

search for cursor + (poor) performance for some horror stories!...(it's probably one of the few times the 2 words actally go in the same sentance!)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

What are you trying to do? Maybe there is a way to avoid a Cursor and a UDF.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mark Giles SSC-Addicted Points: 435 More actions · Answer 5

Frank,

I have a database that represents the relationship between Items in a parent-child hierarchy form. In the parent-child relationships there are Systems at the top and several layers of SubItems below.

My customers need a report that shows what System(s) a given SubItem belong to. For speed purposes, I've used stored procedures and cursors to transform the parent-child relationships into more of a flat form and less of a hierarchical (sp?) form.

If there is another, faster, non-cursor way of doing something like this, I would definitely be interested.

Mark

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 6

post your table structures (DDL), create statements (for sample input data), sample expected results and maybe even your current SP...edited out for identifying(confidential/tradesecret) remarks would be a start.....

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Search the script section here, I bet you'll find something useful. Your problem seems to be nothing exotic.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mark Giles SSC-Addicted Points: 435 More actions · Answer 8

Folks,

The DDL alone is over 400 lines. Is that too many to post?

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 9

AndrewMurphy

SSCertifiable

Points: 5586

April 13, 2004 at 9:15 am

#502584

Not if it helps solve your problem..........

Mark Giles SSC-Addicted Points: 435 More actions · Answer 10

Here it is:

--Tables

CREATE TABLE [dbo].[AssetItemTypes] (

[AssetItemTypeID] [int] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[BaseItems_Systems] (

[SystemBaseItemID] [int] IDENTITY (1, 1) NOT NULL ,

[SystemID] [int] NOT NULL , [BaseItemID] [int] NOT NULL ,

[BaseItemUseID] [int] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ItemTypeUses] (

[ItemTypeUseID] [int] NOT NULL ,

[ItemTypeUse] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ItemTypes] (

[ItemTypeID] [int] NOT NULL ,

[ItemTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SubItems_BaseItems_Systems] (

[SystemBaseItemSubItemID] [int] IDENTITY (1, 1) NOT NULL ,

[SystemID] [int] NOT NULL , [BaseItemID] [int] NOT NULL ,

[BaseItemUseID] [int] NOT NULL , [SubItemID] [int] NOT NULL ,

[SubItemUseID] [int] NOT NULL , [SubItemLevelNumber] [int] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ItemTypeItemTypes] (

[ItemTypeItemTypeID] [int] IDENTITY (1, 1) NOT NULL ,

[NextHigherItemTypeID] [int] NOT NULL , [ItemTypeID] [int] NOT NULL ,

[ItemTypeUseID] [int] NOT NULL

) ON [PRIMARY]

GO

--Views

CREATE VIEW dbo.vwItemTypes

AS

SELECT TOP 100 PERCENT ItemTypeID, ItemTypeName

FROM dbo.ItemTypes

ORDER BY ItemTypeName

GO

CREATE VIEW dbo.vwItemTypesSystems

AS

SELECT TOP 100 PERCENT dbo.ItemTypes.ItemTypeID, dbo.ItemTypes.ItemTypeName, 'System' AS Expr1

FROM dbo.AssetItemTypes INNER JOIN dbo.ItemTypes ON

dbo.AssetItemTypes.AssetItemTypeID = dbo.ItemTypes.ItemTypeID

GO

--Functions

CREATE FUNCTION dbo.udf_Systems()

RETURNS TABLE

AS

RETURN (SELECT TOP 100 PERCENT ItemTypeID, ItemTypeName FROM vwItemTypesSystems

ORDER BY ItemTypeName)

GO

CREATE FUNCTION dbo.udf_HandlingEquip (@ItemTypeID int)

RETURNS TABLE AS

RETURN

(SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN

vwItemTypes ON ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 10))

GO

CREATE FUNCTION dbo.udf_SysComp (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 7))

GO

CREATE FUNCTION dbo.udf_SubItem (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 8))

GO

CREATE FUNCTION dbo.udf_TestSets (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 11))

GO

CREATE FUNCTION dbo.udf_Tools (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 12))

GO

CREATE FUNCTION dbo.udf_UsedWithItem (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN ItemTypeUses ON

ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID INNER JOIN

vwItemTypes ON ItemTypeItemTypes.NextHigherItemTypeID = vwItemTypes.ItemTypeID

WHERE (ItemTypeItemTypes.ItemTypeID = @ItemTypeID))

GO

--Procedures

CREATE PROCEDURE dbo.usp_MakeBaseItem_SystemTable

AS

SET NOCOUNT ON

DECLARE @SystemID int

DECLARE @BaseItemID int

DECLARE @BaseItemUseID int

--create temp table for Level0

CREATE TABLE #Level0(

SystemID int

)

--Initialize Level0 temp table

INSERT #Level0

SELECT DISTINCT ItemTypeID

FROM udf_Systems()

DECLARE System_cursor CURSOR

FOR

SELECT DISTINCT SystemID

FROM #Level0

FOR READ ONLY

--open cursor

OPEN System_cursor

-- cursor operation goes here

-- get the first row

FETCH System_cursor INTO @SystemID

WHILE @@FETCH_STATUS = 0

BEGIN

--create a temp table to store the base items for each system

CREATE TABLE #baseItems(

baseItemsID int,

baseItemsUseID int

&nbsp

--insert handling gear IDs for each system

INSERT #baseItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID

FROM udf_HandlingEquip(@SystemID)

--insert system component IDs for each system

INSERT #baseItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID

FROM udf_SysComp(@SystemID)

--insert testset IDs for each system

INSERT #baseItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID

FROM udf_TestSets(@SystemID)

--insert tool IDs for each system

INSERT #baseItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID

FROM udf_Tools(@SystemID)

--get a cursor for the baseItems ItemTypeIDs

DECLARE baseItems_cursor CURSOR

FOR SELECT baseItemsID, baseItemsUseID FROM #baseItems

FOR READ ONLY

--open cursor

OPEN baseItems_cursor

--cursor operation goes here

--get the first row

FETCH baseItems_cursor INTO @BaseItemID, @BaseItemUseID

WHILE @@FETCH_STATUS = 0

BEGIN

--insert

INSERT BaseItems_Systems(SystemID, BaseItemID, BaseItemUseID)

VALUES(@SystemID, @BaseItemID, @BaseItemUseID)

-- get the next row

FETCH baseItems_cursor INTO @BaseItemID, @BaseItemUseID

END

--close and deallocate cursor

CLOSE baseItems_cursor

DEALLOCATE baseItems_cursor

--drop table

DROP TABLE #baseItems

--get the next row from #Level0

FETCH System_cursor INTO @SystemID

END

--close and deallocate cursor

CLOSE System_cursor

DEALLOCATE System_cursor

--Drop temp tables

DROP TABLE #Level0

GO

CREATE PROCEDURE dbo.usp_deleteBaseItems_Systems

AS

IF EXISTS (SELECT * FROM BaseItems_Systems)

BEGIN

DELETE

FROM BaseItems_Systems

END

GO

CREATE PROCEDURE dbo.usp_deleteSubItems_BaseItems_Systems

AS

IF EXISTS (SELECT * FROM SubItems_BaseItems_Systems)

BEGIN

DELETE

FROM SubItems_BaseItems_Systems

END

GO

CREATE PROCEDURE dbo.usp_PopulateSubItems_Level2

AS

SET NOCOUNT ON

DECLARE @SystemID int

DECLARE @BaseItemID int

DECLARE @BaseItemUseID int

DECLARE @SubItemID int

DECLARE @SubItemUseID int

DECLARE @LevelNumber int

SELECT @LevelNumber = 2

--POPULATE SubItems_BaseItems_Systems Table

--get a cursor for the BaseItems_Systems System and BaseItem IDs

DECLARE System_cursor CURSOR

FOR

SELECT DISTINCT SystemID, BaseItemID, BaseItemUseID

FROM BaseItems_Systems

FOR READ ONLY

--open cursor

OPEN System_cursor

-- cursor operation goes here

-- get the first row

FETCH System_cursor INTO @SystemID, @BaseItemID, @BaseItemUseID

WHILE @@FETCH_STATUS = 0

BEGIN

IF EXISTS(SELECT DISTINCT ItemTypeID, ItemTypeUseID FROM udf_SubItem(@BaseItemID))

BEGIN

--create a temp table to store the sub items for each system

CREATE TABLE #subItems(subItemsID int, subItemUseID int)

--insert base item IDs for each system

INSERT #subItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID FROM udf_SubItem(@BaseItemID)

--get a cursor for the BaseItem ItemTypeIDs

DECLARE subItems_cursor CURSOR

FOR SELECT subItemsID, subItemUseID FROM #subItems

FOR READ ONLY

--open cursor

OPEN subItems_cursor

--get the first row

FETCH subItems_cursor INTO @SubItemID, @SubItemUseID

WHILE @@FETCH_STATUS = 0

BEGIN

--insert BaseItemIDs for each SystemID into BaseItems_Systems

INSERT SubItems_BaseItems_Systems(SystemID, BaseItemID, BaseItemUseID, SubItemID, SubItemUseID, SubItemLevelNumber)

VALUES(@SystemID, @BaseItemID, @BaseItemUseID, @SubItemID, @SubItemUseID, @LevelNumber)

-- get the next row from #baseItems

FETCH subItems_cursor INTO @SubItemID, @SubItemUseID

END

--close and deallocate cursor

CLOSE subItems_cursor

DEALLOCATE subItems_cursor

--drop table for sub items

DROP TABLE #subItems

END

--get the next row from #Level0

FETCH System_cursor INTO @SystemID, @BaseItemID, @BaseItemUseID

END

--close and deallocate cursor

CLOSE System_cursor

DEALLOCATE System_cursor

GO

CREATE PROCEDURE dbo.usp_PopulateSubItemsByLevelNumber

(

@LevelNumber int

&nbsp

AS

SET NOCOUNT ON

DECLARE @SystemID int

DECLARE @BaseItemID int

DECLARE @BaseItemUseID int

DECLARE @SubItemID int

DECLARE @SubItemUseID int

DECLARE @SubItemLevelNumber int

--POPULATE SubItems_BaseItems_Systems Table

--get a cursor for the SubItems_BaseItems_Systems System, BaseItem, and SubItem IDs

DECLARE System_cursor CURSOR

FOR

SELECT DISTINCT SystemID, BaseItemID, BaseItemUseID, SubItemID, SubItemUseID

FROM SubItems_BaseItems_Systems

WHERE SubItemLevelNumber = (@LevelNumber - 1)

FOR READ ONLY

--open cursor

OPEN System_cursor

-- cursor operation goes here

-- get the first row

FETCH System_cursor INTO @SystemID, @BaseItemID, @BaseItemUseID, @SubItemID, @SubItemUseID

WHILE @@FETCH_STATUS = 0

BEGIN

IF EXISTS(SELECT DISTINCT ItemTypeID FROM udf_SubItem(@SubItemID))

BEGIN

--create a temp table to store the sub items for sub items

CREATE TABLE #subItems(subItemsID int, subItemsUseID int)

--insert sub item IDs for each sub item

INSERT #subItems

SELECT DISTINCT ItemTypeID, ItemTypeUseID FROM udf_SubItem(@SubItemID)

--get a cursor for the subItems ItemTypeIDs

DECLARE subItems_cursor CURSOR

FOR SELECT subItemsID, subItemsUseID FROM #subItems

FOR READ ONLY

--open cursor

OPEN subItems_cursor

--get the first row

FETCH subItems_cursor INTO @SubItemID, @SubItemUseID

WHILE @@FETCH_STATUS = 0

BEGIN

--insert BaseItemIDs for each SystemID into BaseItems_Systems

INSERT SubItems_BaseItems_Systems(SystemID, BaseItemID, BaseItemUseID, SubItemID, SubItemUseID, SubItemLevelNumber)

VALUES(@SystemID, @BaseItemID, @BaseItemUseID, @SubItemID, @SubItemUseID, @LevelNumber)

-- get the next row from #subItems

FETCH subItems_cursor INTO @SubItemID, @SubItemUseID

END

--close and deallocate cursor

CLOSE subItems_cursor

DEALLOCATE subItems_cursor

--drop table for sub items

DROP TABLE #subItems

END

--get the next row from SubItems_BaseItems_Systems

FETCH System_cursor INTO @SystemID, @BaseItemID, @BaseItemUseID, @SubItemID, @SubItemUseID

END

--close and deallocate cursor

CLOSE System_cursor

DEALLOCATE System_cursor

GO

CREATE PROCEDURE dbo.usp_MakeSubItems_BaseItems_SystemTable

AS

SET NOCOUNT ON

--Delete all records from BaseItems_Systems

EXEC usp_deleteBaseItems_Systems

--Populate BaseItems_Systems Table

EXEC usp_MakeBaseItem_SystemTable

--Delete all records from SubItems_BaseItems_Sytems

EXEC usp_deleteSubItems_BaseItems_Systems

--Populate the SubItems at Level 2

EXEC usp_PopulateSubItems_Level2

--Populate the SubItems at each successive level

EXEC usp_PopulateSubItemsByLevelNumber 3

EXEC usp_PopulateSubItemsByLevelNumber 4

EXEC usp_PopulateSubItemsByLevelNumber 5

GO

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 11

couple of things......

1. there's next to no difference between your UDF's (udf_SysComp, udf_TestSets, etc)...other than the hard-coded .ItemTypeUseID = xx statements....you could/should make them more generic....to minimise the amount of code you have to manage in the future....these 4/5 could/should be reduced to 1.

Also you have views of views....which doesn't seem to add anything to the functionality....are you using these views elsewhere?

2. re the cursors....there definitely is room for improvement. at no stage are you doing row-to-row comparisioons, or maintaining running totals....and thus everything is capable of being done (far faster...and i'm talking about a scale of 10x here and not fractions/percentages) in a non-cursor solution....

however....without sample input data (and expected results), creating that solution on your behalf is a lot harder....give us a chance to see what the processes do to semi-live/test data....rather than guess/infer at the proceedings.

3. the ddl for these tables...omits INDICES...I PRESUME these tables are indexed to support all the join conditions and where clauses!....otherwise your db/application performance will suck.

Mark Giles SSC-Addicted Points: 435 More actions · Answer 12

Below is an update to some functions:

CREATE FUNCTION dbo.udf_HandlingEquip (@ItemTypeID int)

RETURNS TABLE AS

RETURN

(SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN

vwItemTypes ON ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 6))

GO

CREATE FUNCTION dbo.udf_SysComp (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 2))

GO

CREATE FUNCTION dbo.udf_SubItem (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 3))

GO

CREATE FUNCTION dbo.udf_TestSets (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 4))

GO

CREATE FUNCTION dbo.udf_Tools (@ItemTypeID int)

RETURNS TABLE AS

RETURN (SELECT vwItemTypes.*, ItemTypeUses.ItemTypeUseID, ItemTypeUses.ItemTypeUse

FROM ItemTypeItemTypes INNER JOIN vwItemTypes ON

ItemTypeItemTypes.ItemTypeID = vwItemTypes.ItemTypeID INNER JOIN

ItemTypeUses ON ItemTypeItemTypes.ItemTypeUseID = ItemTypeUses.ItemTypeUseID

WHERE (ItemTypeItemTypes.NextHigherItemTypeID =@ItemTypeID ) AND (ItemTypeItemTypes.ItemTypeUseID = 5))

GO

Below is a population script:

--Insert Records into ItemTypeUses

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (1, 'System')

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (2, 'BaseItem')

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (3, 'SubItem')

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (4, 'TestSet')

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (5, 'Tool')

INSERT ItemTypeUses(ItemTypeUseID, ItemTypeUse)

VALUES (6, 'Handling Equip')

--InsertRecords into ItemTypes

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (1, 'System1')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (2, 'System2')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (3, 'System3')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (4, 'System4')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (5, 'BaseItem1')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (6, 'BaseItem2')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (7, 'BaseItem3')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (8, 'SubItem1')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (9, 'SubItem2')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (10, 'SubItem3')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (11, 'SubItem4')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (12, 'SubItem5')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (13, 'SubItem6')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (14, 'SubItem7')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (15, 'SubItem8')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (16, 'SubItem9')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (17, 'SubItem10')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (18, 'SubItem11')

INSERT ItemTypes(ItemTypeID, ItemTypeName)

VALUES (19, 'SubItem12')

--Insert records into ItemTypeItemTypes