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:
Best Answer
For the purpose of this answer, I am assuming your INSERT statement looks something like this:
So, the first thing your trigger needs to do is to insert the (new) categories into tbCategories:
Now all the category names in the imported dataset can be mapped to their IDs using the tbCategories table. And that is what the trigger should do when inserting the subcategories:
For reference, here is a complete trigger script: