Creating a Table with Duplicate Rows : Temporary Table « Table « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Table
  3. Temporary Table
4> CREATE TABLE Dupes(
5>   ID  int       NOT NULL,
6>   Txt char (10) NOT NULL
7> )
8> GO
1> INSERT Dupes (ID, Txt) VALUES (1, 'x')
2> INSERT Dupes (ID, Txt) VALUES (1, 'a')
3> INSERT Dupes (ID, Txt) VALUES (1, 'x')
4> INSERT Dupes (ID, Txt) VALUES (1, 'x')
5> INSERT Dupes (ID, Txt) VALUES (2, 'b')
6> INSERT Dupes (ID, Txt) VALUES (2, 'x')
7> INSERT Dupes (ID, Txt) VALUES (2, 'b')
8> INSERT Dupes (ID, Txt) VALUES (3, 'c')
9> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> --Creating a Temporary Table of Distinct Rows
4>
5> SELECT ID, Txt INTO  #Singles
6> FROM
7>     Dupes
8> GROUP BY
9>   ID,
10>   Txt
11> HAVING
12>   COUNT (*) > 1;
13> GO

(2 rows affected)
1>
2> --Removing the Duplicates
3> DELETE d FROM Dupes AS D JOIN
4>     #Singles AS S ON  S.ID  = D.ID
5>                   AND S.Txt = D.Txt;
6> GO

(5 rows affected)
1>
2> --Inserting the Former Duplicates
3>
4> INSERT Dupes
5> SELECT
6>   *
7> FROM
8>   #Singles
9>
10> drop table dupes;
11> GO

(2 rows affected)
1>








3.7.Temporary Table
3.7.1.Temporary Tables
3.7.2.Local temporary Table
3.7.3.Using a Temporary Table to Communicate with an EXEC()
3.7.4.A script that creates a global temporary table of random numbers
3.7.5.A script that uses a local temporary table instead of a derived table
3.7.6.SELECT INTO a temporary table
3.7.7.Creating a Table with Duplicate Rows
3.7.8.A global temporary table's name begins with ##.
3.7.9.Creating a Local Temporary Table with a SELECT INTO