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 name in (
Select max (primary key field name) as primary key field name from table name group by other fields havingcount (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 name select * 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:

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, we want to copy fields 1 in Table A, 2 in Table B, and 3 to 4 in table C. No data in the table is required.

How should I use SQL statements?

Consider the following question: is the specified table empty?

/* If Table 4 does not exist */select a. col1, B. col2, C. col3 into table4 from tablea A, tableb B, tablec C where 1 = 0

 

However, if Table 4 already exists, an error is returned. If you want to add these fields to table 4, but the data of these fields does not need to be added to table 4

Select * into Table 5 from table 4 m left join (select. field 1, B. field 2, C. field 3 from Table A, table B, table C where 1 <> 1) n on 1 = 1 drop Table 4 exec sp_rename 'table 5', 'table 4'