Copy data tables and table structures using SQL statements

SQL copy data table (select * into and insert)

Select*Into Target table name From Source table name Insert Into Target table name (fld1, fld2) SelecT fld1, 5From Source table name The above two statements insert data from the source table to the target table, but the two statements are different: The first clause (select into from) requires that the target table does not exist because it is automatically created during insertion. The second sentence (insert into select from) requires that the target table exists. Because the target table already exists, We can insert constants in addition to the fields in the source table, for example: 5.

From: http://hi.baidu.com/minthj/blog/item/47ef6f8bc0ee1a1acafc7a61.html

 

 

Copy the table structure and data SQL statement

Database15:03:34 Read151 Comment0 Font Size:LargeMediumSmall

1: copy the table structure and data to the new table

Select * into target database name. DBO. Target table name from original table name

Select * into my0735home. DBO. infomiantest from infomian

2: Back up some columns of the table (write the list of columns without writing)

Select column name 1, column name 2, column name 3 into destination database name. DBO. destination table name from original table name

Select ID, title, mtype, stype, author, tel, NR into infomiantest2 from infomian

3: Back up some rows of the table (with the where condition)

Select * into target database name. DBO. Target table name from original table name where ID <10

Select * into infomiantest2 from infomian where ID <10

4: Back up some columns of the table (write the column list without writing *) and some rows (add the where condition)

Select column name 1, column name 2, column name 3 into destination database name. DBO. destination table name from original table name where ID <10

5: only copy the table structure: for example, select * inot T1 from titles where 1 = 2

6. the query result is from multiple tables, for example:

Select title_id, title, pub_name into T3

From titles t inner join publishers P

On T. pub_id = P. pub_id

From: http://liujiassd.blog.163.com/blog/static/8311714320094283334854/