USE [Featherman_Analytics]; In this module we will also use the concept of copying data from one data table into another, or from a data table on disk to a data table in memory (the famed SELECT * arraytable or DBA’s call them table variables. In this document data is copied INTO Featherman_Analytics.dbo.ManagersList from a table or set of tables INTO an array. A similar concept is copying data from one table to another. FROM [AdventureWorksDW2012].[dbo].[DimEmployee] WHERE [Title] LIKE '%manager%' You must be the owner of the database to be able create tables in the database to be able to insert/update/delete data. None of us can add tables to AdventureWorksDW2012. This code runs on the Featherman_Analytics database. The code creates the ManagersList table and inserts 20 rows of data into it from the DimEmployee table of AdventireWorksDW2012. This code creates a new table on a disk drive. In this module we will create virtual tables called an array and load data into it. Table int, Here is the basic procedure for creating arraytables (ie, table DECLARE @ArrayTable1 (column1 column2 Step 1 - NVARCHAR(50)) variable), loading them with data and producing analytics. declare the array tables Table int, DECLARE @ArrayTable2 (column1 column2 This query shows one way to combine two tables of retrieved data and their VARCHAR(50)) when you surely have a common field of interest, the primary key. columns First you create two table variables (arrays) and then SELECT values and INSERT them INTO the table variables. Finally you use a INSERT INTO @ArrayTable1 final SELECT statement to pull data from the two table variables Step 2 – SELECT Column1, Sum(Column2) together and to create new columns of derived data so that the Select data FROM SomeTable resultset has all the calculated or aggregate fields. using normal GROUP BY Column1 queries and
USE [Featherman_Analytics]; SELECT * INTO Featherman_Analytics.dbo.ManagersList FROM [AdventureWorksDW2012].[dbo].[DimEmployee] WHERE [Title] LIKE '%manager%' In this module we will also use the concept of copying data from one data table into another, or from a data table on disk to a data table in memory (the famed arraytable or DBA’s call them table variables. In this document data is copied from a table or set of tables INTO an array. A similar concept is copying data from one table to another. You must be the owner of the database to be able create tables in the database to be able to insert/update/delete data. None of us can add tables to AdventureWorksDW2012. This code runs on the Featherman_Analytics database. The code creates the ManagersList table and inserts 20 rows of data into it from the DimEmployee table of AdventireWorksDW2012. This code creates a new table on a disk drive. In this module we will create virtual tables called an array and load data into it. Step 1 - declare the array tables and their columns Step 2 – Select data using normal queries and DECLARE @ArrayTable1 Table (column1 int, column2 NVARCHAR(50)) DECLARE @ArrayTable2 Table (column1 int, column2 VARCHAR(50)) INSERT INTO @ArrayTable1 SELECT Column1, Sum(Column2) FROM SomeTable GROUP BY Column1 Here is the basic procedure for creating arraytables (ie, table variable), loading them with data and producing analytics. This query shows one way to combine two tables of retrieved data when you surely have a common field of interest, the primary key. First you create two table variables (arrays) and then SELECT values and INSERT them INTO the table variables. Finally you use a final SELECT statement to pull data from the two table variables together and to create new columns of derived data so that the resultset has all the calculated or aggregate fields.
Featherman-TSQL-Intermediate-Analytics-Table-Variables-10-2-16.docx