SELECT INTO 和 INSERT INTO SELECT 表复制语句

一、使用 INSERT INTO SELECT 复制表

　　1、INSERT INTO @表变量 SELECT
　　2、INSERT INTO #临时表 SELECT

二、使用 SELECT INTO 复制表
　　3、SELECT INTO #临时表

 

 

1、INSERT INTO @表变量 SELECT 

SET NOCOUNT ON

--INSERT INTO @表变量
DECLARE @tLog TABLE
(
    FID BIGINT PRIMARY KEY,
    FCreateTime DATETIME,
    FCompanyId INT,
    FOperatorId INT,
    FIp VARCHAR(20)
)
INSERT INTO @tLog(FID,FCreateTime,FCompanyId,FOperatorId,FIp)
SELECT FID,FCreateTime,FCompanyId,FOperatorId,FIp
FROM dbo.TAdminOperationLog
WHERE FCompanyId=36052
AND FCreateTime BETWEEN '20160601' AND '20160801'

SELECT * FROM @tLog
ORDER BY FID DESC


SET NOCOUNT OFF

 

2、INSERT INTO #临时表 SELECT

SET NOCOUNT ON


--INSERT INTO #临时表
IF EXISTS  (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempLog') AND type='U')
    DROP TABLE #tempLog

--创建 #临时表
CREATE TABLE #tempLog
(
    FID BIGINT PRIMARY KEY,
    FCreateTime DATETIME,
    FCompanyId INT,
    FOperatorId INT,
    FIp VARCHAR(20)
)
INSERT INTO #tempLog(FID,FCreateTime,FCompanyId,FOperatorId,FIp)
SELECT FID,FCreateTime,FCompanyId,FOperatorId,FIp
FROM dbo.TAdminOperationLog
WHERE FCompanyId=36052
AND FCreateTime BETWEEN '20160601' AND '20160801'

SELECT * FROM #tempLog
ORDER BY FID DESC


--清理临时表
IF EXISTS  (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempLog') AND type='U')
BEGIN
    TRUNCATE TABLE #tempLog
    DROP TABLE #tempLog
END 


SET NOCOUNT OFF

 

3、SELECT INTO #临时表

SET NOCOUNT ON


--SELECT INTO #临时表 
 IF EXISTS  (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAdminLog') AND type='U')
    DROP TABLE #tempAdminLog

SELECT FID,FCreateTime,FCompanyId,FOperatorId,FIp
INTO #tempAdminLog
FROM dbo.TAdminOperationLog
WHERE FCompanyId=36052
AND FCreateTime BETWEEN '20160601' AND '20160801'

SELECT * FROM #tempAdminLog
ORDER BY FID DESC


--清理临时表
IF EXISTS  (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAdminLog') AND type='U')
BEGIN
    TRUNCATE TABLE #tempAdminLog
    DROP TABLE #tempAdminLog
END 


SET NOCOUNT OFF