INSERT INTO statement

The INSERT statement adds one or more new rows to a table. Use a SELECT subquery to specify the data values for one or more rows.

Syntax :
INSERT INTO [existing table] ([column])
SELECT [column name]
FROM [table name]



Example :
INSERT INTO dbo.Shop (ShopNo,ProductNo,Stock)
SELECT ForeignKey, Description
FROM dbo.NewShop

SELECT INTO statement

The SELECT INTO statement creates the new table by copying schema and other information that is not specific to the actual algorithm.

Syntax :
SELECT [column]INTO [new table]FROM [existing table]

Example :
SELECT * INTO dbo.NewShop FROM dbo.Shop

Crosstab Query

Crosstab query :

A cross tab query is a transformation of rows of data to columns.It is easier review any data from rows.

Two examples of a cross-tab is to take the above data and show how much your shops stocked in each product across. This allows for easy comparison of stock by product.

This is source data from dbo.Shop's table.

Example 1 :

Move data in column of ProductNo to column of MLR,MLB and MLC.

SELECT ShopNo,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLR') AS MLR,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLB') AS MLB,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLC') AS MLC
FROM dbo.Shop S GROUP BY ShopNo

Result :

Example 2 :

This is example of summary stock by product.

SELECT ShopNo,

SUM(CASE WHEN ProductNo ='MLR' THEN Stock ELSE 0 END) AS MLR,

SUM(CASE WHEN ProductNo ='MLB' THEN Stock ELSE 0 END) AS MLB,

SUM(CASE WHEN ProductNo ='MLC' THEN Stock ELSE 0 END) AS MLC,

SUM(Stock) AS Total

FROM Shop

GROUP BY ShopNo

Result :