The SQL Server Data row cannot delete or copy the table structure.

The following are the two problems I encountered when working on the system:

<1>. SQL server has duplicate rows of data and cannot be updated or deleted.

It is usually a table without a primary key.

Error:

Check whether the table has a primary key. If not, use a temporary table to delete duplicate rows.

Method 1:
Delete from table name WHERE primary key field nameIN (
Select max (primary key field name) AS primary key field name FROM table name group by other fieldsHAVINGCOUNT (1)> 1)

Method 2:
SELECT field list INTO # t from table name group by Field List
-- Delete the original data table before inserting non-repeated statements.
Truncate table name
Insert into Table NameSELECT * FROM # T
Drop table # T

Example:

Method 1:

Delete from tab_test2 where id = (select max (id) from tab_test2 group by id, uid having count (1)> 1) 

Method 2: Note: # t is a temporary table, which can be found under the temporary table node in the tempdb database and will disappear after the SQL Server service is restarted.

Select id, uid into # t from tab_Test2 group by id, uid

Truncate table tab_Test2

Insert into tab_Test2 select * from # t 

<2>.Copy the structure of other tables to a specified table. 

For example:

There are three tables as follows::

TableA:Field1,Field2,Field3

TableB:Field1,Field2,Field3

TableC:Field1,Field2,Field3

Copy nowATable Fields1, BTable Fields2, CTable Fields3To table4. No data in the table is required.

UseSQLHow Should statements be implemented??

Consider the following question: is the specified table empty?

1. /* When Table 4 does not exist */
3. Select a. col1, B. col2, c. col3
4. Into table4
5. From tableA a, tableB B, tableC c
6. Where1=0

However, onceTable4If it already exists, an error is returned. If you want4But the data of these fields does not need to be added to the table.4Come in

1. Select
2. *
3. Into Table 5
4. From
5. Table 4 m
6. Left join
7. (Select a. Field 1, B. Field 2, c. Field 3 from Table A a, table B, table C c where 1<>1) n
8. On
9. 1=1
11. Drop table 4
13. Exec sp_rename 'table 5', 'table 4'