SQL statement for data backup replication between two tables

The SELECT INTO statement selects data from one table and then inserts the data into another table.

The SELECT into statement is often used to create a backup copy of a table or to archive records.

SQL SELECT into syntax
You can insert all the columns into the new table:

SELECT *
into new_table_name [in Externaldatabase]
From Old_tablename

> CREATE TABLE Employee (
2>     id           int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (a),
7>     region      char (1))
8> go
1>
2> INSERT INTO employee (ID, name,    salary, start_date, city,     & nbsp region)
3>               values (1,   ' Jason ', 40420,  ' 02/01/94 ', ' New York ', ' W '
4> go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> values (2, ' Robert ', 14420, ' 01/02/95 ', ' Vancouver ', ' N ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> values (3, ' Celia ', 24020, ' 12/03/96 ', ' Toronto ', ' W ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> VALUES (4, ' Linda ', 40620, ' 11/04/97 ', ' New York ', ' N ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> values (5, ' David ', 80026, ' 10/05/98 ', ' Vancouver ', ' W ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> VALUES (6, ' James ', 70060, ' 09/06/99 ', ' Toronto ', ' N ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> VALUES (7, ' Alison ', 90620, ' 08/07/00 ', ' New York ', ' W ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> VALUES (8, ' Chris ', 26020, ' 07/08/01 ', ' Vancouver ', ' N ')
3> Go

(1 rows affected)
1> INSERT into employee (IDs, name, salary, Start_date, city, region)
2> VALUES (9, ' Mary ', 60020, ' 06/09/02 ', ' Toronto ', ' W ')
3> Go

(1 rows affected)
1>
2> SELECT * FROM employee
3> Go
ID Name Salary start_date City Region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W

(9 rows affected)
1>
2>
3> SELECT Id, Name
4> into Employee_temp from Employee WHERE Id > 1
5> Go

(8 rows affected)
1>
2> SELECT * from Employee_temp
3> Go
Id Name

Using SELECT INTO to make a temporary table

34> CREATE TABLE works_on (emp_no INTEGER not NULL,
35> project_no CHAR (4) Not NULL,
36> Job CHAR (NULL),
37> enter_date DATETIME NULL)
38>
39> INSERT INTO works_on values (1, ' P1 ', ' analyst ', ' 1997.10.1 ')
40> INSERT INTO works_on values (1, ' P3 ', ' manager ', ' 1999.1.1 ')
41> INSERT INTO works_on values (2, ' P2 ', ' clerk ', ' 1998.2.15 ')
42> INSERT INTO works_on values (2, ' P2 ', NULL, ' 1998.6.1 ')
43> INSERT into works_on values (3, ' P2 ', NULL, ' 1997.12.15 ')
44> INSERT INTO works_on values (4, ' P3 ', ' analyst ', ' 1998.10.15 ')
45> INSERT into works_on values (5, ' P1 ', ' manager ', ' 1998.4.15 ')
46> INSERT INTO works_on values (6, ' P1 ', NULL, ' 1998.8.1 ')
47> INSERT INTO works_on values (7, ' P2 ', ' clerk ', ' 1999.2.1 ')
48> INSERT INTO works_on values (8, ' P3 ', ' clerk ', ' 1997.11.15 ')
49> INSERT INTO works_on values (7, ' P1 ', ' clerk ', ' 1998.1.4 ')
50> 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 rows affected)

(1 rows affected)

(1 rows affected)
1> SELECT * from works_on
2> Go
Emp_no Project_no Job Enter_date
----------- ---------- --------------- -----------------------
1 P1 analyst 1997-10-01 00:00:00.000
1 P3 manager 1999-01-01 00:00:00.000
2 P2 clerk 1998-02-15 00:00:00.000
2 P2 NULL 1998-06-01 00:00:00.000
3 P2 NULL 1997-12-15 00:00:00.000
4 P3 analyst 1998-10-15 00:00:00.000
5 P1 manager 1998-04-15 00:00:00.000
6 P1 NULL 1998-08-01 00:00:00.000
7 P2 Clerk 1999-02-01 00:00:00.000
8 P3 Clerk 1997-11-15 00:00:00.000
7 P1 Clerk 1998-01-04 00:00:00.000

(Rows affected)
1>
2>--Remove duplicate data and create a unique index
3>
4> SELECT emp_no, MAX (enter_date) max_date
5> into #works_on
6> from works_on
7> GROUP by Emp_no
8> having COUNT (*) > 1
9> Go

(3 rows affected)
1> DELETE works_on from works_on, #works_on
2> WHERE works_on.emp_no = #works_on. Emp_no
3> and Works_on.enter_date < #works_on. max_date
4> Go

(3 rows affected)
1> SELECT * from works_on
2> Go
Emp_no Project_no Job Enter_date
----------- ---------- --------------- -----------------------
1 P3 manager 1999-01-01 00:00:00.000
2 P2 NULL 1998-06-01 00:00:00.000
3 P2 NULL 1997-12-15 00:00:00.000
4 P3 analyst 1998-10-15 00:00:00.000
5 P1 manager 1998-04-15 00:00:00.000
6 P1 NULL 1998-08-01 00:00:00.000
7 P2 Clerk 1999-02-01 00:00:00.000
8 P3 Clerk 1997-11-15 00:00:00.000

(8 rows affected)

Note: SELECT into must be two tables of the results of complete consistency can also be good to complete the data back OH.