March 17, 2009 at 4:39 pm
That WAS quick, Jeff.
Fox, here is the deal. An INSERT INTO can come just before a SELECT Statement, and everything in the result statement feeds into the INSERT. All the rows at once.
Result columns can be the orignal values from the source tables, computed from expressions (even conditional expressions, see CASE), functions, subqueries, or constants. There is a tremendous amount of power there.
It's easier and takes fewer lines of code. No need to go through the declaration, the looping, the test to see if the next row was returned. It's nowhere near as complicated as using the Cursor AND it's faster.
You should be very close to having that light bulb come on. THAT is set based processing. Any loop that processes one row at a time and does one insert at a time is procedural.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:42 pm
I will take the 5th on this one Bob before I am accused again of being insulting.
March 17, 2009 at 4:43 pm
Just saw your repost with the corrected SQL, fox. He started with your code, and you had that commented out. Garbage in, garbage out, remember?
I'm betting someone is going to throw a row_Number() function into the query to handle the row incrementing. Now pizza and film are calling. Goodnight all.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:48 pm
Ok, I messed up.. The table I sent has and identity spec, I thought it didn't.
Some of the tables I have don't use ident.
So take out the identity spec when creating the table. Then answer the question.
March 17, 2009 at 4:50 pm
So a row_number function would work... That sounds like a plan.
March 17, 2009 at 4:52 pm
Fox, I promise you, I would much rather we all got out of the insult mode, and got into constructive problem solving. It's really a lot more enjoyable. People who volunteer LIKE sharing their knowledge and helping others. But we also like doing it for people who are at least pleasant and courteous.
My tone was meant to be encouraging. I truly think you are about to have a moment of enlightenment. That does NOT require you to prefer Begin... End to {}. 😉 SQL may change in the future to suit you, and it may not. Right now it is what it is. If you want to learn more about it, we can help make your life easier. If not, the argument is a dead issue.
I feel strongly enough about burying the hatchet to stop and type this up, at the risk of my wife killing me if I show up late with cold pizza. Why don't you meet me halfway?
Peace. Out.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:56 pm
[font="Verdana"]Don't need a cursor for that. Look up the row_number() function in SQL Server Books Online.[/font]
March 17, 2009 at 5:22 pm
just a bit more complicated:
db scripts...
USE [PDS]
GO
/****** Object: Table [dbo].[PrdIchAttributeTypes] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdIchAttributeTypes](
[PrdIchAttributeTypeID] [int] NOT NULL,
[PrdIchAttributeType] [nvarchar](255) NOT NULL,
[Sequence] [int] NOT NULL,
CONSTRAINT [PK_PrdIchAttributeTypes] PRIMARY KEY CLUSTERED
(
[PrdIchAttributeTypeID] 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
/****** Object: Table [dbo].[PrdIchAttributes] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdIchAttributes](
[PrdIchAttributeID] [int] IDENTITY(1,1) NOT NULL,
[PrdIchAttribute] [nvarchar](50) NOT NULL,
[PrdIchAttributeTypeID] [int] NOT NULL,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PrdStatus] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdStatus](
[ProductStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProductStatus] [nvarchar](100) NOT NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[Include] [bit] NULL,
[ChangedMachineName] [varchar](50) NULL,
[ChangedUserName] [varchar](50) NULL,
CONSTRAINT [PK_ProductStatus] PRIMARY KEY CLUSTERED
(
[ProductStatusID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdIchComments] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdIchComments](
[PrdIchCommentID] [int] IDENTITY(1,1) NOT NULL,
[PrdIchComment] [nvarchar](2048) NOT NULL,
[Symbol] [nvarchar](100) NULL,
[ChangedDate] [datetime] NULL,
[ChangedMachineName] [nvarchar](50) NULL,
[ChangedUserName] [nvarchar](50) NULL,
CONSTRAINT [PK_PrdIchComments] PRIMARY KEY CLUSTERED
(
[PrdIchCommentID] 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
/****** Object: Table [dbo].[PrdExtraStatus] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdExtraStatus](
[HasApplication] [bit] NOT NULL,
[HasAttribute] [bit] NOT NULL,
[HasInterchange] [int] NOT NULL,
[ProductID] [int] NOT NULL,
CONSTRAINT [PK_PrdExtraStatus] PRIMARY KEY CLUSTERED
(
[ProductID] 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
/****** Object: Table [dbo].[PrdLineGroup] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdLineGroup](
[ProductLineGroupID] [int] NOT NULL,
[ProductLineGroup] [varchar](50) NOT NULL,
[Sequence] [int] NULL,
CONSTRAINT [PK_PrdLineGroup] PRIMARY KEY CLUSTERED
(
[ProductLineGroupID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdCrossRefHow] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdCrossRefHow](
[ProductCrossRefHowID] [smallint] NOT NULL,
[ProductCrossRefHow] [varchar](50) NOT NULL,
[ProductCrossRefHowDescription] [varchar](255) NULL,
CONSTRAINT [PK_ProductInterchangeType] PRIMARY KEY CLUSTERED
(
[ProductCrossRefHowID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdIchCommentQualifier] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdIchCommentQualifier](
[PrdIchQualifierID] [int] NOT NULL,
[PrdIchQualifier] [nvarchar](255) NOT NULL,
[PrdIchCommentID] [int] NOT NULL,
CONSTRAINT [PK_PrdIchCommentQualifier] PRIMARY KEY CLUSTERED
(
[PrdIchQualifierID] 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
/****** Object: Table [dbo].[PrdAttributeType] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdAttributeType](
[ProductAttributeTypeID] [int] NOT NULL,
[ProductAttributeType] [varchar](50) NOT NULL,
CONSTRAINT [PK_ProductAttributeType] PRIMARY KEY CLUSTERED
(
[ProductAttributeTypeID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdLineDomain] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdLineDomain](
[GenProductLineDomainID] [int] NOT NULL,
[ProductLineDomain] [varchar](50) NOT NULL,
[Sequence] [int] NULL,
[ChangedMachineName] [nvarchar](50) NULL,
[ChangedType] [char](1) NULL,
[ChangedUserName] [nvarchar](50) NULL,
[ChangedDate] [datetime] NULL,
CONSTRAINT [PK_ProductLineDomain] PRIMARY KEY CLUSTERED
(
[GenProductLineDomainID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdCrossList] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdCrossList](
[ProductCrossListID] [int] NOT NULL,
[ProductCrossList] [nvarchar](50) NOT NULL,
[Sequence] [smallint] NOT NULL,
CONSTRAINT [PK_CrossList] PRIMARY KEY CLUSTERED
(
[ProductCrossListID] 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
/****** Object: Table [dbo].[PrdCategoryType] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdCategoryType](
[ProductCategoryTypeDesc] [varchar](50) NOT NULL,
[ProductCategoryTypeID] [int] NOT NULL,
[Flex] [nvarchar](255) NULL,
[ChangedMachineName] [nvarchar](50) NOT NULL,
[ChangedType] [char](1) NOT NULL,
[ChangedDate] [datetime] NOT NULL,
[ChangedUserName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_CategoryConfig] PRIMARY KEY CLUSTERED
(
[ProductCategoryTypeID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdLine] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdLine](
[ProductLineID] [int] IDENTITY(1,1) NOT NULL,
[ProductLine] [nvarchar](255) NOT NULL,
[Alias] [varchar](100) NULL,
[TamsCode] [char](5) NULL,
[ProductLineDomainID] [int] NULL,
[Bookmark] [bit] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[ChangedUserName] [nvarchar](50) NULL,
[ChangedMachineName] [nvarchar](50) NULL,
CONSTRAINT [PK_ProductLine] PRIMARY KEY CLUSTERED
(
[ProductLineID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Prd] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Prd](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductLineID] [int] NOT NULL,
[PartNumber] [nvarchar](50) NOT NULL,
[SearchNumber] [nvarchar](50) NOT NULL,
[ProductStatusID] [int] NOT NULL,
[DateStatusChanged] [datetime] NULL,
[Publish] [bit] NULL,
[UPC] [varchar](50) NULL,
[CurrentProductNumber] [nchar](10) NULL,
[DateAdded] [datetime] NULL,
[Description] [nvarchar](255) NULL,
[History] [text] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[ChangedMachineName] [nvarchar](50) NULL,
[ChangedUserName] [nvarchar](50) NULL,
[AnticipatedInventory] [datetime] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdLineGroupRel] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdLineGroupRel](
[ProductLineGroupID] [int] NOT NULL,
[ProductLineID] [int] NOT NULL,
[Sequence] [int] NOT NULL,
CONSTRAINT [PK_PrdLineGroupRel] PRIMARY KEY CLUSTERED
(
[ProductLineGroupID] ASC,
[ProductLineID] 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
/****** Object: Table [dbo].[PrdCrossRef] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdCrossRef](
[ProductCrossID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ProductCrossRefHowID] [smallint] NOT NULL,
[ProductCrossListID] [int] NOT NULL,
[HasApplication] [bit] NULL,
[ShowOnInterchange] [bit] NULL,
[ShowOnPriceSheet] [bit] NULL,
[AttributeID] [int] NULL,
[NumberRequired] [int] NULL,
[CommentID] [int] NULL,
[AutoApply] [bit] NULL,
[ChangedDate] [datetime] NOT NULL,
[ChangedUserName] [nvarchar](50) NULL,
[ChangedMachineName] [nvarchar](50) NULL,
CONSTRAINT [PK_ProductInterchange] PRIMARY KEY CLUSTERED
(
[ProductCrossID] ASC,
[ProductID] 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
/****** Object: Table [dbo].[PrdCategory] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdCategory](
[ProductCategoryDesc] [nvarchar](150) NOT NULL,
[ParentID] [int] NULL,
[ProductCategoryTypeID] [int] NOT NULL,
[ProductCategoryID] [int] IDENTITY(1,1) NOT NULL,
[ProductLineID] [int] NULL,
[Sequence] [int] NULL,
[Abbr] [nvarchar](50) NULL,
[Alias] [nvarchar](100) NULL,
[Flex] [nvarchar](255) NULL,
[HasRelation] [bit] NOT NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[ChangedMachineName] [nvarchar](50) NULL,
[ChangedUserName] [nvarchar](50) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[ProductCategoryID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdAttributeName] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdAttributeName](
[ProductAttributeNameID] [int] IDENTITY(2,1) NOT NULL,
[ProductAttributeName] [varchar](50) NOT NULL,
[ProductAttributeTypeID] [int] NULL,
[ProductCategoryID] [int] NOT NULL,
[ProductValidationType] [nvarchar](50) NULL,
[ProductValidValues] [nvarchar](1000) NULL,
[Sequence] [int] NULL,
[ChangedDate] [datetime] NOT NULL,
[ChangedType] [char](1) NOT NULL,
[ChangedmachineName] [nvarchar](50) NULL,
[ChangedUserName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductAttributeName] PRIMARY KEY CLUSTERED
(
[ProductAttributeNameID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdAttribute] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdAttribute](
[ProductAttributeID] [int] IDENTITY(1,1) NOT NULL,
[ProductAttribute] [varchar](255) NOT NULL,
[ProductAttributeNameID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ChangedDate] [datetime] NOT NULL,
[ChangedType] [char](1) NOT NULL,
[ChangedMachineName] [varchar](50) NOT NULL,
[ChangedUserName] [varchar](50) NOT NULL,
CONSTRAINT [PK_ProductAttribute] PRIMARY KEY CLUSTERED
(
[ProductAttributeID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdCategoryPrdRel] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrdCategoryPrdRel](
[ProductID] [int] NOT NULL,
[ProductCategoryID] [int] NOT NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[ChangedMachineName] [nvarchar](50) NULL,
[ChangedUserName] [nvarchar](50) NULL,
CONSTRAINT [PK_CategoryProductRel] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[ProductCategoryID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PrdTypeGeneric] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrdTypeGeneric](
[ProductdLineID] [int] NOT NULL,
[PrroductTypeGenericID] [int] NOT NULL,
[ProductCategoryID] [int] NOT NULL,
CONSTRAINT [PK_PrdTypeGeneric] PRIMARY KEY CLUSTERED
(
[PrroductTypeGenericID] 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
/****** Object: Table [dbo].[prdProductNumber] Script Date: 03/17/2009 17:21:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[prdProductNumber](
[ProductNumber] [nvarchar](50) NULL,
[ProductID] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_prdProductNumber] PRIMARY KEY CLUSTERED
(
[ID] 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
/****** Object: Default [DF_Product_ProductStatusID] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[Prd] ADD CONSTRAINT [DF_Product_ProductStatusID] DEFAULT ((1)) FOR [ProductStatusID]
GO
/****** Object: Default [DF_ProductLine_Bookmark] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdLine] ADD CONSTRAINT [DF_ProductLine_Bookmark] DEFAULT ((0)) FOR [Bookmark]
GO
/****** Object: Check [CK_product_and_line] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[prdProductNumber] WITH CHECK ADD CONSTRAINT [CK_product_and_line] CHECK (([dbo].[CheckDupProduct]([ProductNumber],[ProductID])=(0)))
GO
ALTER TABLE [dbo].[prdProductNumber] CHECK CONSTRAINT [CK_product_and_line]
GO
/****** Object: ForeignKey [FK_Product_ProductLine] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[Prd] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductLine] FOREIGN KEY([ProductLineID])
REFERENCES [dbo].[PrdLine] ([ProductLineID])
GO
ALTER TABLE [dbo].[Prd] CHECK CONSTRAINT [FK_Product_ProductLine]
GO
/****** Object: ForeignKey [FK_Product_ProductStatus] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[Prd] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductStatus] FOREIGN KEY([ProductStatusID])
REFERENCES [dbo].[PrdStatus] ([ProductStatusID])
GO
ALTER TABLE [dbo].[Prd] CHECK CONSTRAINT [FK_Product_ProductStatus]
GO
/****** Object: ForeignKey [FK_ProductAttribute_Product] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdAttribute] WITH CHECK ADD CONSTRAINT [FK_ProductAttribute_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Prd] ([ProductID])
GO
ALTER TABLE [dbo].[PrdAttribute] CHECK CONSTRAINT [FK_ProductAttribute_Product]
GO
/****** Object: ForeignKey [FK_ProductAttribute_ProductAttributeName1] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdAttribute] WITH CHECK ADD CONSTRAINT [FK_ProductAttribute_ProductAttributeName1] FOREIGN KEY([ProductAttributeNameID])
REFERENCES [dbo].[PrdAttributeName] ([ProductAttributeNameID])
GO
ALTER TABLE [dbo].[PrdAttribute] CHECK CONSTRAINT [FK_ProductAttribute_ProductAttributeName1]
GO
/****** Object: ForeignKey [FK_ProductAttributeName_Category] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdAttributeName] WITH CHECK ADD CONSTRAINT [FK_ProductAttributeName_Category] FOREIGN KEY([ProductCategoryID])
REFERENCES [dbo].[PrdCategory] ([ProductCategoryID])
GO
ALTER TABLE [dbo].[PrdAttributeName] CHECK CONSTRAINT [FK_ProductAttributeName_Category]
GO
/****** Object: ForeignKey [FK_ProductAttributeName_ProductAttributeType1] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdAttributeName] WITH CHECK ADD CONSTRAINT [FK_ProductAttributeName_ProductAttributeType1] FOREIGN KEY([ProductAttributeTypeID])
REFERENCES [dbo].[PrdAttributeType] ([ProductAttributeTypeID])
GO
ALTER TABLE [dbo].[PrdAttributeName] CHECK CONSTRAINT [FK_ProductAttributeName_ProductAttributeType1]
GO
/****** Object: ForeignKey [FK_Category_CategoryType] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCategory] WITH NOCHECK ADD CONSTRAINT [FK_Category_CategoryType] FOREIGN KEY([ProductCategoryTypeID])
REFERENCES [dbo].[PrdCategoryType] ([ProductCategoryTypeID])
GO
ALTER TABLE [dbo].[PrdCategory] CHECK CONSTRAINT [FK_Category_CategoryType]
GO
/****** Object: ForeignKey [FK_CategoryProductRel_Category] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCategoryPrdRel] WITH NOCHECK ADD CONSTRAINT [FK_CategoryProductRel_Category] FOREIGN KEY([ProductCategoryID])
REFERENCES [dbo].[PrdCategory] ([ProductCategoryID])
GO
ALTER TABLE [dbo].[PrdCategoryPrdRel] CHECK CONSTRAINT [FK_CategoryProductRel_Category]
GO
/****** Object: ForeignKey [FK_CategoryProductRel_Product] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCategoryPrdRel] WITH CHECK ADD CONSTRAINT [FK_CategoryProductRel_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Prd] ([ProductID])
GO
ALTER TABLE [dbo].[PrdCategoryPrdRel] CHECK CONSTRAINT [FK_CategoryProductRel_Product]
GO
/****** Object: ForeignKey [FK_CrossRef_CrossList] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCrossRef] WITH CHECK ADD CONSTRAINT [FK_CrossRef_CrossList] FOREIGN KEY([ProductCrossListID])
REFERENCES [dbo].[PrdCrossList] ([ProductCrossListID])
GO
ALTER TABLE [dbo].[PrdCrossRef] CHECK CONSTRAINT [FK_CrossRef_CrossList]
GO
/****** Object: ForeignKey [FK_CrossRef_CrossRefType] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCrossRef] WITH CHECK ADD CONSTRAINT [FK_CrossRef_CrossRefType] FOREIGN KEY([ProductCrossRefHowID])
REFERENCES [dbo].[PrdCrossRefHow] ([ProductCrossRefHowID])
GO
ALTER TABLE [dbo].[PrdCrossRef] CHECK CONSTRAINT [FK_CrossRef_CrossRefType]
GO
/****** Object: ForeignKey [FK_CrossRef_Product] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdCrossRef] WITH CHECK ADD CONSTRAINT [FK_CrossRef_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Prd] ([ProductID])
GO
ALTER TABLE [dbo].[PrdCrossRef] CHECK CONSTRAINT [FK_CrossRef_Product]
GO
/****** Object: ForeignKey [FK_ProductLine_ProductLineDomain] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdLine] WITH CHECK ADD CONSTRAINT [FK_ProductLine_ProductLineDomain] FOREIGN KEY([ProductLineDomainID])
REFERENCES [dbo].[PrdLineDomain] ([GenProductLineDomainID])
GO
ALTER TABLE [dbo].[PrdLine] CHECK CONSTRAINT [FK_ProductLine_ProductLineDomain]
GO
/****** Object: ForeignKey [FK_PrdLineGrpRel_PrdLine] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdLineGroupRel] WITH CHECK ADD CONSTRAINT [FK_PrdLineGrpRel_PrdLine] FOREIGN KEY([ProductLineID])
REFERENCES [dbo].[PrdLine] ([ProductLineID])
GO
ALTER TABLE [dbo].[PrdLineGroupRel] CHECK CONSTRAINT [FK_PrdLineGrpRel_PrdLine]
GO
/****** Object: ForeignKey [FK_PrdLineGrpRel_PrdLineGroup] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdLineGroupRel] WITH CHECK ADD CONSTRAINT [FK_PrdLineGrpRel_PrdLineGroup] FOREIGN KEY([ProductLineGroupID])
REFERENCES [dbo].[PrdLineGroup] ([ProductLineGroupID])
GO
ALTER TABLE [dbo].[PrdLineGroupRel] CHECK CONSTRAINT [FK_PrdLineGrpRel_PrdLineGroup]
GO
/****** Object: ForeignKey [FK_PrdTypeGeneric_PrdCategory] Script Date: 03/17/2009 17:21:45 ******/
ALTER TABLE [dbo].[PrdTypeGeneric] WITH CHECK ADD CONSTRAINT [FK_PrdTypeGeneric_PrdCategory] FOREIGN KEY([ProductCategoryID])
REFERENCES [dbo].[PrdCategory] ([ProductCategoryID])
GO
ALTER TABLE [dbo].[PrdTypeGeneric] CHECK CONSTRAINT [FK_PrdTypeGeneric_PrdCategory]
GO
March 17, 2009 at 5:24 pm
Given the above scripts:
here is the cursor script that will populate one of the tables.
This can easily be done with a setbase solution????????????
:w00t:
set nocount on
delete PrdAttribute
DBCC CHECKIDENT ('dbo.prdattribute', RESEED, 1)
declare @attribute nvarchar(50)
declare @productid int
declare @attributenameid int
declare @productline nvarchar(50)
declare @PartNumber nvarchar(50)
declare @attributename nvarchar(50)
declare @cattype nvarchar(255)
declare @catid int
declare @sel cursor
set @sel = cursor for
SELECT DISTINCT [Product Attribute],[Product Line],[Part Number],[Product Type],[Product Specification]
FROM APAPartSource.dbo.ProductAttributeData
open @sel
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
while (@@FETCH_STATUS = 0)
begin
select @catid = ProductCategoryID from prdcategory where productcategorydesc = @cattype
select @attributeNameid = ProductAttributeNameID from prdattributename where ProductAttributeName = @attributeName and ProductCategoryID = @catid and productattributetypeid = 1
select @productid = ProductID from PrdInfo where partnumber = @partnumber and productline = @productline
insert into PrdAttribute ( ProductAttribute, ProductAttributeNameID, ProductID, changeddate,changedtype,changedmachinename, changedusername)
values (@attribute,@attributeNameid,@productid,getdate(),'I','Refresh','Refresh')
fetch next from @sel into @attributename,@productline,@PartNumber, @cattype,@attribute
end
close @sel
March 17, 2009 at 5:24 pm
foxjazz (3/17/2009)
Ok, I messed up.. The table I sent has and identity spec, I thought it didn't.Some of the tables I have don't use ident.
So take out the identity spec when creating the table. Then answer the question.
This is simply hilarious !
The identity function was indeed there all the time and was not even noticed. So now, in order to "prove" the point that only a cursor will do, downgrade the table to avoid using IDENTITY. Avoiding the convenience of IDENTITY to generate the primary keys. There obviously was NO business rule prohibiting its use but just remove it...
As for the other tables which do not use identity, then HOW are their primary keys generated?
Again, you do not NEED "no stinkin' cursors". If you look at the link I posted earlier, Jeff Moden shows how to generate sequence without looping. Do yourself a favor and look at this article, you WILL learn something useful. Jeff Moden also wrote another excellent article on Tally tables. Easy to implement and lightning fast. The time you have already spent to justify your refusal to listen to very experienced persons is probably greater than the time to read and play around with these two articles.
March 17, 2009 at 5:30 pm
I agree. If I can do more stuff without the use of cursors, I am happier.
Some things just have to be looped. Like for instance, sequence numbers.
but we will get to that jazz later.
March 17, 2009 at 5:43 pm
[font="Verdana"]Not quite sure why sequence numbers "need to be looped"...? With the right design (or often even with the right query overlaying a poor design), you can allocate sequence numbers as necessary as part of a set.[/font]
March 17, 2009 at 9:02 pm
You know, its too bad you can't even post code that works. I tried to run all the code you provided to set up the test, and it failed.
I'll leave it to you to find out what is wrong with your code. I also have a suggestion for you, before you post any more code on this forum, you should test it first.
Also, we need test data for all the tables. Be sure to provide that in a format that only requires it to be cut, pasted, and executed in SSMS or EM.
March 17, 2009 at 9:32 pm
Also, I need to verify one thing, as you have this posted in a SQL Server 7, 2000 forum you are using SQL Server 2000, correct? If so, the reference to row_number() won't help you. That was something added in SQL Server 2005.
March 17, 2009 at 9:41 pm
By the way, my error and my apology for mentioning row_number(). This has gone on for so long, that I forgot which forum(s) we were in. We could get there with the intermediate step of a temporary table, which COULD have an identity column 😉 , and still avoid cursors. Would probably still run faster. I can't test that out tonight. I'm on my home PC, which has nothing to code in beyond VBA.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 196 through 210 (of 465 total)
You must be logged in to reply to this topic. Login to reply