Mainstream database copy table structure (sqlserver/MySQL/Oracle/Sybase) [syntax Difference Analysis]

Because the database SQL statement in the major products with "dialect", that is, SQL Server Sybase are using T-SQL, MySQL is using standard SQL, Oracle has its own PL/SQL. Due to the differences in dialects, many SQL statements are difficult to transplant, and they are embarrassing in heterogeneous databases. Of course, DBA is nothing more than checking the syntax. But it is inconvenient. Here we will share with you the syntax analysis in the databases where I copied the table structure, that is, the data.

Let's talk about SQL Server's most familiar experience.

-- If there is no new table in the database, such as table A and Table B.

Select * into B from a -- Copy table and Data

Select * into B from a where 1> 1 -- only copy the table structure

-- If table B already exists in the data, it is not applicable.

Insert into B select * from a -- Copy table data, to avoid. Theoretically, table B should have no data. If yes, it may cause a violation of the primary key.

Insert into B select * from a where ID> 10 -- With the where condition, you can specify the data to be copied. This can also be done without a table.

Here, Sybase and sqlserver are the same. Because in a way, Sybase is the prototype of SQL.

 

 

Next is MySQL. The syntax is slightly different.

-- The database does not contain table B.

Create Table B select * from

Create Table B select * from a where 1 <> 1 -- only copy the table structure

-- Tables B are basically the same as those of sqlserver.

Insert into B select * from

 

Oracle is similar to MySQL, but the syntax requirements are more rigorous. Of course, MySQL can also be written like this.

-- The database does not contain table B.

Create Table B as select * from

Create Table B as select * from a where 1 <> 1 -- only copy the table structure

Create Table B like a -- Copy table structure

-- Tables B are basically the same

Insert into B select * from

These are relatively basic SQL statements, but they are also applicable. In the future, we will analyze some other commonly used SQL statements.