T-SQL——關於表資料的複製插入

shanzm發表於2021-10-02
SQL

目錄

志銘-2021年10月2日 22:21:00

0. 複製表中一列插入到另外一列

--將col2列值複製給col1
UPDATE table_name SET col1_name=col2_name
WHERE col1_condition

1. 複製表結構和資料到自動建立的一張新表中——select into

當需要備份某張表的記錄的時候，常常需要根據現有的表複製一個備份表

下面示例，從表table1複製出一張表table2

  • table_name2 會自動建立，所以不需要 也不可以 事先建立好
  • 從table1複製到table2中的僅僅是指定欄位和欄位的值，不包含主鍵、索引、約束和觸發器,所以千萬不要通過此種方式建立一個相同欄位，相同功能的表（若是需要還是選中表右鍵-->編寫指令碼為-->create到
--複製部分的欄位
SELECT col1,col2,col3…… INTO table_name2 FROM table_name1
--複製所以的欄位
SELECT * INTO table_name2 FROM table_name1
--複製部分記錄
SELECT * INTO table_name2 FROM table_name1 WHERE table_name1_col_conditionc
--只複製表的欄位名，不復制資料（即:建立一個空表）
SELECT * INTO table_name2 FROM table_name1 WHERE 1<>1
SELECT TOP 0 INTO table_name2 FROM table_name1
  • SELECT INTO語句可以複製資料到一個自動建立的臨時表中
  • SELECT INTO語句不支援表變數，既不能自動建立一個表變數，也不能將資料複製到一個已宣告的表變數中
IF OBJECT_ID('tempdb.. #tempTable') IS NOT NULL
BEGIN
    DROP TABLE #tempTable;
END;

SELECT * INTO  #tempTable
FROM table1;

SELECT * FROM #tempTable
DROP TABLE #tempTable

2. 複製表中一些欄位值插入到另外一張表中——insert into

複製表table1中某幾列資料插入（更新）到表table2中的某幾列

  • table2必須已經存在，其欄位不需要和table1中一樣
  • table2可以是已建立的臨時表，也可以是已宣告的表變數
INSERT INTO table2_name(col1,col2,col3)
SELECT col1,col2,col3 FROM table1
WHERE table1_condition

3. 將儲存過過程結果儲存在臨時表或表變數中

將儲存過程的結果儲存在臨時表，需要使用insert into，即需要使用先建立臨時表的方式

  • 注意一定：定義的臨時表的欄位需要和儲存過程返回的結果集的欄位完全一樣，不能多也不能少

示例:

  1. 準備測試表及資料
--準備測試表及資料
CREATE TABLE Person
(
    [Id] INT,
    [Name] NVARCHAR(50),
    [Age] INT,
    [ClassId] NVARCHAR(50)
);
INSERT INTO Person
VALUES
(2, N'Jerry', 7, N'11'),
(3, N'Bob', 7, N'11'),
(4, N'Allen', 7, N'11'),
(5, N'Andy', 8, N'22'),
(6, N'Beck', 8, N'22'),
(7, N'Billy', 8, N'22');
  1. 建立一個簡單的儲存過程
--建立一個儲存過程
CREATE PROCEDURE pro_GetPerson
AS
BEGIN
    SELECT Name,Age FROM Person
END
  1. 執行存過並將結果儲存到臨時表
--執行儲存過程將結果儲存在臨時表中
----建立臨時表
IF OBJECT_ID('tempdb..#Person') IS NOT NULL
BEGIN 
DROP TABLE #Person
END
CREATE TABLE #Person
(
    Name NVARCHAR(50),
    AGE INT
)
----執行儲存過程並儲存到臨時表中
INSERT INTO #Person Exec pro_GetPerson
SELECT * FROM #Person
  1. 執行存過並將結果賦值給表變數
----定義表變數並將儲存過程的結果賦值給表變數
DECLARE @Person TABLE
(
    Name NVARCHAR(50),
	Age INT
)
INSERT INTO	@Person EXEC dbo.pro_GetPerson
SELECT * FROM @Person

若是希望避免手動建表或手動建立表變數，而使用select into的方式
則需要通過OPENROWSET方法。

除非純粹就是臨時從一個表往另外一個表導資料，不要在儲存過程中使用以下方法

  • 注意：使用OPENROWSET執行儲存過程是無法執行帶有引數的儲存過程的

示例：

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END

SELECT * INTO #temp
FROM
    OPENROWSET('sqloledb', 'server=severname;uid=uid;pwd=password', 'exec 資料庫名.dbo.pro');--注：此方法執行儲存過程無法帶引數
SELECT * FROM #temp;

4. 將動態SQL語句的結果儲存在臨時表中

實現方法和上述執行儲存過程儲存到臨時表和表變數的方法一樣

--動態SQL語句
DECLARE @sqlStr VARCHAR(5000) = 'SELECT Name FROM Company';
--表變數
DECLARE @tableVar TABLE
(
    Name VARCHAR(100)
);
--執行動態SQL將結果儲存在表變數中
INSERT INTO @tableVar
EXEC (@sqlStr);

SELECT * FROM @tableVar;

5. 參考

相關文章