将数据插入临时表

After having created a temporary table and declaring the data types like so;

创建临时表并声明此类数据类型之后;

 CREATE TABLE #TempTable(
 ID int,
 Date datetime,
 Name char(20))

How do I then insert the relevant data which is already held on a physical table within the database?

然后如何插入已经保存在数据库中的物理表上的相关数据?

12 个解决方案

#1

182

INSERT INTO #TempTable (ID, Date, Name) 
SELECT id, date, name 
FROM physical_table

#2

SELECT  ID , Date , Name into #temp from [TableName]

#3

To insert all data from all columns, just use this:

要插入来自所有列的所有数据，只需使用以下方法:

SELECT * INTO #TempTable
FROM OriginalTable

Don't forget to DROP the temporary table after you have finished with it and before you try creating it again:

不要忘记在完成临时表之后和尝试重新创建临时表之前删除临时表:

DROP TABLE #TempTable

#4

My way of Insert in SQL Server. Also I usually check if a temporary table exists.

我在SQL Server中插入的方式。我通常还会检查临时表是否存在。

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP Table #MyTable

SELECT b.Val as 'bVals'
  INTO #MyTable
FROM OtherTable as b

#5

SELECT * 
INTO #TempTable
FROM table

#6

The right query:

正确的查询:

drop table #tmp_table

select new_acc_no, count(new_acc_no) as count1
into #tmp_table
from table
where unit_id = '0007' 
group by unit_id, new_acc_no
having count(new_acc_no) > 1

#7

After you create the temp table you would just do a normal INSERT INTO () SELECT FROM

在创建临时表之后，只需在()SELECT中执行一个普通的插入操作

INSERT INTO #TempTable (id, Date, Name)
SELECT t.id, t.Date, t.Name
FROM yourTable t

#8

insert into #temptable (col1, col2, col3)
select col1, col2, col3 from othertable

Note that this is considered poor practice:

注意，这被认为是不好的做法:

insert into #temptable 
select col1, col2, col3 from othertable

If the definition of the temp table were to change, the code could fail at runtime.

如果临时表的定义要更改，那么代码在运行时可能会失败。

#9

INSERT INTO #TempTable(ID, Date, Name)
SELECT OtherID, OtherDate, OtherName FROM PhysicalTable

#10

I have provided two approaches to solve the same issue,

我提供了两种方法来解决同一个问题，

Solution 1: This approach includes 2 steps, first create a temporary table with specified data type, next insert the value from the existing data table.

解决方案1:该方法包含两个步骤，首先创建一个具有指定数据类型的临时表，然后从现有数据表中插入值。

CREATE TABLE #TempStudent(tempID  int, tempName  varchar(MAX) )
INSERT INTO #TempStudent(tempID, tempName) SELECT id, studName FROM students where id =1

SELECT * FROM #TempStudent

Solution 2: This approach is simple, where you can directly insert the values to temporary table, where automatically the system take care of creating the temp table with the same data type of original table.

解决方案2:这种方法很简单，您可以直接将值插入到临时表中，在临时表中，系统会自动地使用原始表的相同数据类型创建临时表。

SELECT id, studName  INTO #TempStudent FROM students where id =1

SELECT * FROM #TempStudent

#11

insert #temptable
select idfield, datefield, namefield from yourrealtable

#12

Basic operation of Temporary table is given below, modify and use as per your requirements,

临时表的基本操作如下，根据您的要求进行修改和使用，

-- CREATE A TEMP TABLE

——创建一个临时表

CREATE TABLE #MyTempEmployeeTable(tempUserID  varchar(MAX), tempUserName  varchar(MAX) )

-- INSERT VALUE INTO A TEMP TABLE

——将值插入临时表

INSERT INTO #MyTempEmployeeTable(tempUserID,tempUserName) SELECT userid,username FROM users where userid =21

-- QUERY A TEMP TABLE [This will work only in same session/Instance, not in other user session instance]

——查询一个临时表[它只在相同的会话/实例中工作，而不在其他用户会话实例中工作]

SELECT * FROM #MyTempEmployeeTable

-- DELETE VALUE IN TEMP TABLE

——删除TEMP表中的值

DELETE FROM #MyTempEmployeeTable

-- DROP A TEMP TABLE

——删除一个临时表。

DROP TABLE #MyTempEmployeeTable

#1

182

INSERT INTO #TempTable (ID, Date, Name) 
SELECT id, date, name 
FROM physical_table

#2

SELECT  ID , Date , Name into #temp from [TableName]

#3