sql两表之间数据备份复制的sql语句

利用select into 做一个临时表

34> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
35> project_no CHAR(4) NOT NULL,
36> job CHAR (15) 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

(11 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)

注间：select into必须两个表的结果完整一致能也很好的把数据完整备份哦。

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com