I have 2 tables, Categories, and SubCategories:
CREATE TABLE [dbo].[tbCategories]
(
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](250) NULL,
)
CREATE TABLE [dbo].[tbSubCategories]
(
[SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NULL,
[SubCategory] [varchar](250) NULL
)
I want to Import Sub-Categories with a csv file, which contains the CategoryName and the SubCategory.
Here's an example:
So I get the CategoryName and the Sub-Category Name. But I need the CategoryID. The user won't know what that is, they will only know the Category Name. What would be this best way to do this?
I already have a bulk insert method (using c#) that works fine if I import a csv file with CategoryID and SubCategoryName. I thought I can insert into a view to achieve this. Something like:
CREATE VIEW [dbo].[vSubCategories]
AS
SELECT c.CategoryName, s.SubCategory
FROM dbo.tbCategories c
INNER JOIN dbo.tbSubCategories s
ON c.CategoryID = s.CategoryID
Then I can use a trigger with 'INSTEAD OF INSERT'. But how do I get SQL to automatically pick up the CategoryID which should be inserted into SubCategory table?
In short, I want to import the csv file above, and see something similar in the tbSubCategories table as below: