March 14, 2011 at 11:06 am
Ok, so I've been a member for a while and read articles as I have time or need. Only posted a few times because the majority of the time I can find the solution to my problem.
However, I'm a bit stumped as to how to get going on this issue. I think it's because I've been doing a ton of code in VB.Net and I've always found T-SQL to be very different from VB. The approach to SQL always seems to be backasswards to me compared to VB or C#.
I've got a table called Products which contains product information. One of the fields I originally created was called CategoryID and referred to a table call Category. Initially, a one to one relationship was fine... but as things would have it, times have changed and I now need to be able to map each product to multiple categories. Not a big deal... I created a ProductCategory table with a field for the ProductID and a field for the CategoryID.
My problem comes in creating new records for all the existing products, so I thought I ask for some help from the brillant people here at SQL ServerCentral.
I've included code to create a test Product, Category and ProductCategory tables with a small amount of data. I'm looking to create an entry in the ProductCategory table for each Product.
This maybe insanely simple, but as I said, I just can't get my head around it at the moment.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_Category] 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
INSERT INTO Category ([Name]) VALUES ('Clothing')
INSERT INTO Category ([Name]) VALUES ('T-Shirts')
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](128) NOT NULL,
[CategoryID] [int] NOT NULL,
CONSTRAINT [PK_Product] 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
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([ID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]
GO
INSERT INTO Product ([ProductName],[CategoryID]) VALUES ('Mustang T-Shirt',(SELECT ID FROM Category WHERE Category.Name = 'Clothing'))
INSERT INTO Product ([ProductName],[CategoryID]) VALUES ('Camero T-Shirt',(SELECT ID FROM Category WHERE Category.Name = 'Clothing'))
INSERT INTO Product ([ProductName],[CategoryID]) VALUES ('Corvett T-Shirt',(SELECT ID FROM Category WHERE Category.Name = 'Clothing'))
INSERT INTO Product ([ProductName],[CategoryID]) VALUES ('280-Z T-Shirt',(SELECT ID FROM Category WHERE Category.Name = 'Clothing'))
CREATE TABLE [dbo].[ProductCategory](
[ProductID] [int] NOT NULL,
[CategoryID] [int] NOT NULL
) ON [PRIMARY]
GO
March 14, 2011 at 11:52 am
Nice job posting your ddl and data in a consumable format. KUDOS!!!!!!!!!
insert ProductCategory
select ID, CategoryID from product
Is that what you are looking for? Also i assume you are planning to CategoryID from the Product table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2011 at 12:17 pm
Sean Lange (3/14/2011)
Nice job posting your ddl and data in a consumable format. KUDOS!!!!!!!!!
insert ProductCategory
select ID, CategoryID from product
Is that what you are looking for? Also i assume you are planning to CategoryID from the Product table?
Duh...
As I said, I've been coding in VB a lot as of late and looping is implied in SQL and sometimes that screws me up.
As for the ddl and data... I picked up on that last time I posted. Was the only way to make sure everyone would understand what I was asking for. Thanks.
As for the old CategoryID field, yes I am planning on not using it any longer and would like to remove it, although that may cause some issues in my VB code. I would guess that it would be better to remove the column if at all possible.
March 14, 2011 at 12:27 pm
Just my 2¢, but I would remove it and fix the VB code that is referencing it. If you still have code that is using it you run the risk of getting a product category that is no longer valid. Glad it worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2011 at 12:33 pm
Sean Lange (3/14/2011)
Just my 2¢, but I would remove it and fix the VB code that is referencing it. If you still have code that is using it you run the risk of getting a product category that is no longer valid. Glad it worked for you.
Your 2¢ may prevent a lot of hand wrings and hair pulling.
I'm actually doing an upgrade of some software along with SQL changes, so I'm building my SQL change scripts and will be releasing a new version of the software. It will be easy to test to make sure I'm not referencing that old field.
Sorry about such a simple questions... was just not coming to me.
March 14, 2011 at 12:38 pm
Your 2¢ may prevent a lot of hand wrings and hair pulling.
I'm actually doing an upgrade of some software along with SQL changes, so I'm building my SQL change scripts and will be releasing a new version of the software. It will be easy to test to make sure I'm not referencing that old field.
Sorry about such a simple questions... was just not coming to me.
Having had many hand wrings and hair pulling in the past i am happy to help keep someone else's hair from turning gray like mine is starting to do. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply