SELECT title_id,title,price INTO t2 FROM titles—部分列 
SELECT * INTO t2 FROM titles WHREE price>10 –部分行 
SELECT title_id,title,price INTO t2 FROM titles WHREE price>10 –部分行和部分列

3．只復制表的結(jié)構(gòu)：

SELECT * INTO tablename_new FROM tablename_old

如：SELECT * INOT t1 FROM titles WHERE 1=2

4．查詢結(jié)果來源于多個表：如：

SELECT title_id,title,pub_name INTO t3 
FROM titles t INNER JOIN publishers p 
ON t.pub_id=p.pub_id

5.select * into 要復制到的數(shù)據(jù)庫名.DBO.表名 from 原數(shù)據(jù)庫名.DBO.表名

從news表復制指定字段到news1

set IDENTITY_INSERT [dbo].[news1] ON
INSERT INTO [dbo].[news1] ([id],[title],[stitle],[type_id],[scontent],[keyword],[tag],[author]) SELECT [id],[title],[stitle],[type_id],[scontent],[keyword],[tag],[author] 

FROM [dbo].[news]
set IDENTITY_INSERT [dbo].[news1] OFF

2.從news表復制id,content字段到content_1(id指定區(qū)間)

create table content_1([id] [int] primary key NOT NULL, [content] [text] NULL)
INSERT INTO [dbo].[content_1] ([id],[content] ) SELECT [id],[content] FROM [dbo].[news] where [id] between 1 and 100000
create table content_2([id] [int] primary key NOT NULL, [content] [text] NULL)
INSERT INTO [dbo].[content_2] ([id],[content] ) SELECT [id],[content] FROM [dbo].[news] where [id] between 100001 and 200000
create table content_3([id] [int] primary key NOT NULL, [content] [text] NULL)
INSERT INTO [dbo].[content_3] ([id],[content] ) SELECT [id],[content] FROM [dbo].[news] where [id] > 200000

數(shù)據(jù)庫復制表

數(shù)據(jù)庫把一張表完整無缺的復制到另外一張表中

簡單的：

insert into 目標表 select * from 原表;

（兩張表結(jié)構(gòu)必須相同，這種方法限制太多）

SqlServer 復制表結(jié)構(gòu)與復制數(shù)據(jù)

1、復制表結(jié)構(gòu)

select [col1,col2,col3...] into new_table 
from old_table where 1=1 ;

一般都是

SELECT * INTO tablename_new FROM tablename_old

2、復制數(shù)據(jù)

insert into table1(col1,col2,col3,...) 
select [col1,col2,col3...] from table2 ;

這樣比圖文方法要簡單多了，效率也高。

到此這篇關(guān)于sql復制表結(jié)構(gòu)和數(shù)據(jù)的實現(xiàn)方法的文章就介紹到這了,更多相關(guān)復制表結(jié)構(gòu)和數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: