von_blog

出處: https://dotblogs.com.tw/mis0800/2014/02/09/143889

 

1.只複製結構，不複製資料 
select * into 新table from 舊table where 1=0 

2.複製結構也複製資料 
select * into 新table from 舊table 

3.指定匯入某欄位的資料至新的資料表(指令一執行即產生新table) 
select a,b,c into 新table from 舊table 

  

select ac1,ac2,bc1,bc2 into table_c 
from table_a 
join table_b on aid=bid

4.將A table某欄位資料複製到B table某欄位 
update B 
set B's column= (select a's column from A where A's column=B's column) 

5.選取B_able某欄位匯入資料至「已存在的A_table」的欄位中
Insert into A_table(A.欄1,A.欄2)  select (B.欄1,B.欄2) from B_able

 

====================================================================================

出處: https://dotblogs.com.tw/fangfang/2012/08/24/74252

[SQL]複製一筆資料並自行指定主鍵值

最近剛好有用到... ↓

 

若[Index]具識別屬性：

                SET IDENTITY_INSERT [dbo].[Table] OFF
                GO
                INSERT INTO [Table] 
                (
                    [index]
                    ,[A]
                    ,[B]
                )
                SELECT 
                    3 AS [index]
                    ,[A]
                    ,[B]
                  FROM [Table]
                  WHERE [index] = 2
                GO  
                SET IDENTITY_INSERT [dbo].[Table] ON
                GO

 

若[Index]不具識別屬性：

                INSERT INTO [Table] 
                (
	                [index]
                    ,[A]
                    ,[B]
                )
                SELECT 
                    3 AS [index]
                    ,[A]
                    ,[B]
                  FROM [Table]
                  WHERE [index] = 2