0ra-00905: Missing keyword
The two tables are used to copy data. The most common copy statements are insert into select and select into from.
Note:
In Oracle, select into from cannot be used! The reason is simple: select into is a value assignment statement for PL/SQL language! Oracle will throw the 0ra-00905: Missing keyword exception if you use it!
However, you can use create table select to replace this function!
But it can be used normally in SQL Server.
Select ID, count (1) as num from tablename group by ID having count (1)> 1
Order by num DESC
"Repeat record" has two Repeated Records. One is a completely repeated record, that is, records with all fields being duplicated, and the other is records with repeated key fields, for example, the name field is repeated, while other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.
Select distinct * From tablename
You can get the result set without repeated records.
If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:
Select distinct * into # TMP from tablename
Drop table tablename
Select * into tablename from # TMP
Drop table # TMP
The reason for this repetition is that the table design is not weekly. You can add a unique index column.
2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.
Select Identity (INT, 1, 1) as autoid, * into # TMP from tablename
Select min (autoid) as autoid into # tmp2 from # TMP group by name, autoid
Select * from # TMP where autoid in (select autoid from # tmp2)
The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)
Summary:
We recommend that you use insert into select to copy data;