Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

Temporary tables are created in tempdb. If you run this query:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

select name
from tempdb..sysobjects 
where name like '#yak%'

drop table #yaks

Another Example

CREATE TABLE #TibetanYaks(
YakID int,
YakName char(30) )

INSERT INTO #TibetanYaks (YakID, YakName)
SELECT 	YakID, YakName
FROM 	dbo.Yaks
WHERE 	YakType = 'Tibetan'

-- Do some stuff with the table

drop table #TibetanYaks

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory.  The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT 	YakID, YakName
FROM 	dbo.Yaks
WHERE 	YakType = 'Tibetan'

-- Do some stuff with the table

Example

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT 	YakID, YakName
FROM 	dbo.Yaks
WHERE 	YakType = 'Tibetan'

UPDATE 	@TibetanYaks
SET 	YakName = UPPER(YakName)

SELECT *
FROM @TibetanYaks

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.  These are rarely used in SQL Server.

Summary

That shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.

Based on: http://www.sqlteam.com/article/temporary-tables

This article refers to AdventureWorks2014 of Microsoft.

You can download AdventureWorks2014 DB here.


At the following picture there are 4 ranking functions at MS SQL:

1. Row_number – Numbering each row.

2. Rank – According to example below when postal code change then the function displays the value of row_number at that position.

3. Dense_Rank – According to example below when postal code change then the function increases.

4. NTile – Divide the results into groups, the example shows division to 4 groups.

Ranking Ranking Functions
Ranking Functions

You can copy and paste the following text into you MS SQL management studio if order to test it:

SELECT     p.firstname,
 p.lastname ,
 Row_number() OVER (ORDER BY a.postalcode) AS “row number” ,
 rank() OVER (ORDER BY a.postalcode)       AS rank ,
 dense_rank() OVER (ORDER BY a.postalcode) AS “dense rank” ,
 ntile(4) OVER (ORDER BY a.postalcode)     AS quartile ,
 s.salesytd ,
 a.postalcode
 FROM       sales.salesperson AS s
 INNER JOIN person.person     AS p
 ON         s.businessentityid = p.businessentityid
 INNER JOIN person.address AS a
 ON         a.addressid = p.businessentityid
 WHERE      territoryid IS NOT NULL
 AND        salesytd <> 0;
Ranking Functions
Ranking Functions

1. Create the following Stored Procedure at the database you want to search:

CREATE PROCEDURE SearchTables 
 @Tablenames VARCHAR(500) 
,@SearchStr NVARCHAR(60) 
,@GenerateSQLOnly Bit = 0 
AS 

/* 
    Parameters and usage 

    @Tablenames        -- Provide a single table name or multiple table name with comma seperated.  
                        If left blank , it will check for all the tables in the database 
    @SearchStr        -- Provide the search string. Use the '%' to coin the search.  
                        EX : X%--- will give data staring with X 
                             %X--- will give data ending with X 
                             %X%--- will give data containig  X 
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.  
                        By default it is 0 and it will search. 

    Samples : 

    1. To search data in a table 

        EXEC SearchTables @Tablenames = 'T1' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in table T1 with string containing TEST. 

    2. To search in a multiple table 

        EXEC SearchTables @Tablenames = 'T2' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in tables T1 & T2 with string containing TEST. 

    3. To search in a all table 

        EXEC SearchTables @Tablenames = '%' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in all table with string containing TEST. 

    4. Generate the SQL for the Select statements 

        EXEC SearchTables @Tablenames        = 'T1' 
                         ,@SearchStr        = '%TEST%' 
                         ,@GenerateSQLOnly    = 1 

*/ 

    SET NOCOUNT ON 

    DECLARE @CheckTableNames Table 
    ( 
    Tablename sysname 
    ) 

    DECLARE @SQLTbl TABLE 
    ( 
     Tablename        SYSNAME 
    ,WHEREClause    VARCHAR(MAX) 
    ,SQLStatement   VARCHAR(MAX) 
    ,Execstatus        BIT  
    ) 

    DECLARE @sql VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 

    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
    BEGIN 

        INSERT INTO @CheckTableNames 
        SELECT Name 
          FROM sys.tables 
    END 
    ELSE 
    BEGIN 

        SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 

        INSERT INTO @CheckTableNames 
        EXEC(@sql) 

    END 

    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT SCh.name + '.' + ST.NAME, 
            ( 
                SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
                ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; 

      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 

      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 

    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 

        SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 



         IF @GenerateSQLOnly = 0 
         BEGIN 

            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 
            EXEC (@SQL) 

            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                SELECT Tablename=@tmpTblname,* FROM SearchTMP 
            END 

         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@sql,'INTO SearchTMP','') 
         END 

         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 

    END 

    SET NOCOUNT OFF 

go

2. Run the following stored procedure as follows:

DECLARE @return_value int

EXEC @return_value = [dbo].[SearchTables]
 @Tablenames = N'%',
 @SearchStr = N'%TextToSearch%',
 @GenerateSQLOnly = 0

SELECT 'Return Value' = @return_value

A great guide for learning SQL from the start, from Udemy:

https://blog.udemy.com/beginners-guide-to-sql/

What Wikipedia has to say about UDemy:

Udemy.com is a platform or marketplace for online learning. Unlike academic MOOC programs driven by traditional collegiate coursework, Udemy provides a platform for experts of any kind to create courses which can be offered to the public, either at no charge or for a tuition fee.[1]Udemy provides tools which enable users to create a course, promote it and earn money from student tuition charges.

In addition to SQL You will find there also the following tutorials:

 

From kudvenkat:

Over 12 years of experience, with Microsoft .NET technologies like ASP.NET, C#, SQL Server, AJAX, WCF, JQuery, SSIS, SSAS and SSRS. Currently working as a Technical Architect in central London. Love to share knowledge as I believe in “TO TEACH IS TO LEARN”.

https://www.youtube.com/user/kudvenkat

Got the opportunity to work on world’s largest e-commerce and banking web applications that are highly transactional in nature. Now, it’s my turn to give something back to the community, hence this video tutorial.

 

 

Microsoft SQL Server Analysis Services, SSAS, is an online analytical processing (OLAP) and data mining tool inMicrosoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors – Multidimensional and Tabular (from wikipedia).

Learn SSAS: PDF, Videos and Demo files:

01. Download SSAS Tutorials PDF file from Microsoft
02. Adventure Works for SQL Server 2012 from CodePlex
03. SSAS 11 Videos of PCTeach.me:
04http://pcteach.me/Series/microsoft-ssas/
05. 6 Lessons of SSAS MDX
06. 7 Videos of SSAS MDX

SSAS Videos at PCTeach.me:

01. Analysis Services - 01 Prerequisite Guide
02. Analysis Services - 02 Data Source Creation
03. Analysis Services - 03 Data Source Views
04. Analysis Services - 04 Cube Creation
05. Analysis Services - 05 Dimension Fundamentals
06. Analysis Services - 06 Dimension Hierarchies
07. Analysis Services - 07 Dimension Attribute Relationships
08. Analysis Services - 08 Dimension Storage
09. Analysis Services - 09 Dimension Discretization
10. Analysis Services - 10 Parent/Child Dimension Hierachies
11. Analysis Services - 11 Star and Snowflake Schemas

 

ALTER PROCEDURE [dbo].[Mulsp] @Size INT
AS
BEGIN
DECLARE @X INT=2
DECLARE @Y INT=1

BEGIN try
DROP TABLE [dbo].[multable]
END try

BEGIN catch
END catch

CREATE TABLE [dbo].[multable]
(
c1 INT
)

–create the rows 
DECLARE @CN NVARCHAR(max)=
DECLARE @TSql NVARCHAR(max)

WHILE @x <= @Size
BEGIN
SELECT @cn = ‘c’ + Cast(@x AS NVARCHAR)

PRINT @cn

SET @TSql=‘alter table MulTable add ‘ + @cn + ‘ int’

PRINT @Tsql

EXEC sys.Sp_sqlexec
@Tsql

SET @x=@x + 1
END

–at this point build each line query 
SET @X=1

DECLARE @EachLineQuery NVARCHAR(max)

WHILE @Y <= @Size
BEGIN
SET @EachLineQuery=‘insert into MulTable values (‘

WHILE @x < @Size
BEGIN
SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR)
+ ‘,’
SET @x=@x + 1
END

SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR)
+ ‘)’

EXEC sys.Sp_sqlexec
@EachLineQuery

PRINT @EachLineQuery

SET @EachLineQuery=
SET @y=@y + 1
SET @x=1
END

SELECT *
FROM   multable
END

— Create TEMP Table Local

— Disappears when windows of query is closed.

 CREATE TABLE #tmp_table  (
id   INT,
NAME VARCHAR(20)
SELECT 1      AS ID,
‘John’ AS NAME

— Create And Insert Into Temp Table

INSERT INTO #tmp_table
 SELECT *
 FROM   #tmp_table
 

— Temp Table Global 

CREATE TABLE ##tmp_global_table -- Create TEMP Table
  (
     id   INT,
 
     NAME VARCHAR(20)
  )
INSERT INTO ##tmp_global_table
VALUES      (1,'One')
SELECT *
FROM   ##tmp_global_table 
 

 

This demo s pretty self explained, you just copy and paste it into SQL Management studio:

DECLARE @cmd NVARCHAR(30) = 'SELECT * FROM Person.Address' 
EXEC (@cmd) 
go
-- Using Dynamic SQL With Parameter 
DECLARE @cmd_param VARCHAR = 1 
DECLARE @cmd VARCHAR(100) = N'Select * from employees where employeeid = ' 
  + @cmd_param 

EXEC (@cmd)
-- DDL - Create Table 
DECLARE @cmd VARCHAR(100) 
SET @cmd = 'Create table TBL (col1 int)' 
EXEC (@cmd) 
SELECT * 
FROM   tbl;
-- DROP 
DROP TABLE tbl
--- sp_executesql --- 
DECLARE @cmd NVARCHAR(100) 

SET @cmd = 
'SELECT * FROM Person.Address where AddressID = @1 and PostalCode = @2' 

EXEC Sp_executesql 
  @cmd, 
  N'@1 int, @2 nvarchar(15)', 
  1, 
  '98011' 

go

In Visual Studio 2013 you could double click a SDF database file within Solution Explorer and it would automatically open the database file and allow you to view the database tables, add tables, edit data etc.

In Visual Studio 2013 this native support seems to have ‘fallen off’, double clicking an SDF file simply assumes it is yet another binary object:

VS2013 native 'support' for SDF files (none)

go to the MSDN Gallery and get the SQL Server Compact Toolbox, here:

http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/

once installed you can drag the new “SQL Server Compact Toolbox” window and dock it where it used to be:

(It suppose to be At Main Menu->Tools->Server Compact)

VS 2013 with the SQL Server Compact Toolbox add in installed