Q: How to copy the structure of other tables into a specified table, such as the following example:
The following three tables are available:
Table A: Field 1, Field 2, Field 3
Table B: Field 1, Field 2, Field 3
Table C: Field 1, Field 2, Field 3
Now you want to copy the field of Table 1,b table field 2,c table field 3 to table 4. The data in the table is not required.
How should SQL statements be implemented?
A: In fact, here is a question to consider, the specified table is not an empty table? Therefore, we have the following two kinds of circumstances, respectively, said:
1,/*table4 does not exist when * *
Select A.col1, B.col2, c.col3
Into Table4
From TableA A, TableB B, TableC c
where 1 = 0
2, however, once the table 4 already exists, will be an error. If you want to add several fields based on table 4, and then copy all the fields to another table, the data for those fields does not need to be added to the table
SELECT * into table 5 from table 4 m
Left Join
(Select a. field 1,b. field 2,c. field 3 from Table A, table b b, table C c where 1<>1) n
On 1=1
DROP table 4
EXEC sp_rename ' table 5 ', ' Table 4 '
Transfer from http://blog.163.com/zhaoyanping_1125/blog/static/20132915320120645416135/
SQL Server Replication Table