CODEWITHSUNDEEP
sqlsql-server
David Richardson
David Richardson

Reputation: 21

SQL INSERT INTO FROM SELECT

I have a database created on Microsoft SQL Server 2012, I have two tables "dbo.Products" and "dbo.Categories". I am trying to query these two tables to create a PRODUCT dimension table for a star schema.

Products {ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued}
Categories {CategoryID, CategoryName, Description}

These two tables are from the Northwind database

The Product Dimension contains a product_key, productID, productName, categoryID, categoryName and categoryDescription which are queried from the Northwind Products/Categories tables.

The SQL code I am using is here:

/*Create Dimension Table */
CREATE TABLE [dbo].[PRODUCT] (
[product_key] [int] IDENTITY (1, 1) NOT NULL Primary Key,
[productID] [int] NULL ,
[productName] nvarchar(30) NULL ,
[categoryID] [int] NULL ,
[categoryName] nvarchar(30) NULL ,
[categoryDescription] nvarchar(30) NULL 
) 

/* Populate (insert) Dimension Table with a select statement that joins 
Product and Categories. */
INSERT INTO [dbo].[PRODUCT] (productID, productName, categoryID, categoryName, categoryDescription)
   SELECT 
      ProductID, ProductName, CategoryID, CategoryName, CategoryDescription
   FROM 
      Products 
   INNER JOIN 
      Categories ON Categories.CategoryID = Products.CategoryID;

Upvotes: 1

Views: 1144

Answers (2)

rs.
rs.

Reputation: 27437

Try this

INSERT INTO [acs4904_a1].[dbo].[PRODUCT] 
(productID, productName, categoryID, categoryName, categoryDescription)

SELECT 
P.ProductID, 
P.ProductName, 
C.CategoryID, 
C.CategoryName, 
C.CategoryDescription
FROM [acs4904_a1].[dbo].Products P 
INNER JOIN [acs4904_a1].[dbo].Categories C ON P.CategoryID=C.CategoryID;

Upvotes: 1

Michael Dunlap
Michael Dunlap

Reputation: 4310

You have to scope the shared columns.

INSERT INTO [PRODUCT] (productID, productName, categoryID, categoryName, categoryDescription)
SELECT ProductID, ProductName, Products.CategoryID, CategoryName, CategoryDescription
FROM Products 
INNER JOIN Categories ON Categories.CategoryID=Products.CategoryID;

Upvotes: 0

Related Questions