无论 SQL Server 中的列名如何，都将整行选择到另一个表中

Question

有没有办法从临时表（表只有一行）中选择行到另一个具有不同名称的列的表中？例如：

TempTable

FirstName        LastName          Column1       Column2
------------    ------------     -----------   -----------
Joe              Smith             OKC           TX

OtherTable

FirstName       LastName          Region1        Region2        Region3
------------    ------------     -----------   -----------     ----------
NULL              NULL             NULL           NULL           NULL

我需要按照与 TempTable 中的列相同的顺序将数据复制到 OtherTable 中。 TempTable 并不总是相同......因为有时它有 3 列，有时只有 2 列......等等。如果它的列数与 OtherTable 不同，则其余“Region”列应保持为空。

最终结果应该是：

OtherTable

FirstName       LastName         Region1       Region2         Region3
------------    ------------     -----------   -----------     ----------
Joe              Smith             OKC           TX               NULL

另外，TEMPTable 中的列名称永远不会相同...因为一次它会是“Column1”...下一次可能是“XXXXX1” 。这就是为什么我只想复制数据...数据将始终按正确的顺序...

哈哈...这有意义吗？这是针对 SQL Server 2005

Answer 1

编辑........添加动态SQL生成

此代码将生成INSERT语句以从#TEMP插入#TEMP。如果您要从 #temp 转到常规表，您可以对其进行调整以适合您的目的。

SET NOCOUNT ON
DROP Table #TempTable1
DROP Table #TempTable2
GO
DROP Function GenerateInserts
GO
Create Function GenerateInserts
(
    @SourceTable    VarChar (100),
    @DestinationTable   VarChar (100)
)
Returns VarChar (MAX)
AS
BEGIN

DECLARE @SelectString VarChar (MAX)
DECLARE @InsertString VarChar (MAX)
DECLARE @SQLString VarChar (MAX)

DECLARE @SourceColumnCount  INTEGER
DECLARE @DestinationColumnCount INTEGER
DECLARE @ColumnCount    INTEGER
DECLARE @Counter    INTEGER

SELECT @SourceColumnCount = COUNT (*)
FROM tempdb..syscolumns 
WHERE id=object_id(@SourceTable)

SELECT @DestinationColumnCount = COUNT (*)
FROM tempdb..syscolumns 
WHERE id=object_id(@DestinationTable)

SET @ColumnCount = @SourceColumnCount

IF @DestinationColumnCount < @ColumnCount
    SET @ColumnCount = @DestinationColumnCount

SET @Counter = 0

SET @SelectString = ' INSERT INTO ' + @DestinationTable + ' '
SET @InsertString = ' INSERT INTO ' + @DestinationTable + ' '

SET @SelectString = ''
SET @InsertString = ''

WHILE @Counter <= @ColumnCount
BEGIN
    SELECT @SelectString = @SelectString  + ', ' + Name
    FROM TempDB..SysColumns 
    WHERE Id = Object_Id (@SourceTable)
    AND ColOrder = @Counter

    SELECT @InsertString = @InsertString  + ', ' + Name
    FROM TempDB..SysColumns 
    WHERE Id = Object_Id (@DestinationTable)
    AND ColOrder = @Counter

    SET @Counter = @Counter  + 1
END

SET @InsertString = 'INSERT INTO ' + @DestinationTable + ' (' +  STUFF (    @InsertString, 1, 2, '') + ') '
SET @SelectString = 'SELECT ' +  STUFF (    @SelectString, 1, 2, '') + ' FROM ' + @SourceTable

SET @SQLString = @InsertString + '
'+ @SelectString

RETURN @SQLString
END

GO

Create Table #TempTable1
(
    Col1 VarChar (10), 
    Col2 VarChar (10), 
    Col3 VarChar (10),
    Col4 VarChar (10), 
    Col5 VarChar (10) 
)
Create Table #TempTable2
(
    MyCol1 VarChar (10), 
    MyCol2 VarChar (10), 
    MyCol3 VarChar (10),
    MyCol4 VarChar (10), 
    MyCol5 VarChar (10), 
    MyCol6 VarChar (10)
)

SELECT dbo.GenerateInserts ('tempdb..#TempTable1', 'tempdb..#TempTable2')

---

---

---

旧答案

是的，您可以执行此操作，但您必须为每种类型的 INSERT 编写不同的语句。您必须在两个位置指定列名称 - INSERT INTO 和 SELECT

---

如果源表和目标表中的列数相同，请执行以下

INSERT INTO Table1 (Column1, Column2, Column3)
SELECT MyColumn01, MyColumn02, MyColumn03
FROM MyTable 

操作要做的就是映射如下：

MyTable.MyColumn01 ->表1.Column1
MyTable.MyColumn02 ->表1.列2
MyTable.MyColumn03 -> Table1.Column3

---

如果源表的列数较少，则可以使用 NULL 值代替列名

INSERT INTO Table1 (Column1, Column2, Column3)
SELECT MyColumn01, MyColumn02, NULL AS MyColumn03
FROM MyTable 

或者，您可以只使用两个列名

INSERT INTO Table1 (Column1, Column2)
SELECT MyColumn01, MyColumn02
FROM MyTable 

---

如果目标表的列数少于源表，则您可以必须忽略源中的列

INSERT INTO Table1 (Column1, Column2, Column3)
SELECT MyColumn01, MyColumn02, NULL AS MyColumn03 /* MyColumn04, MyColumn05 are ignored */
FROM MyTable 

Answer 2

您可以使用动态 SQL 做一些事情。

我建议阅读“动态 SQL 的诅咒和祝福 -
如果您不熟悉的话，处理动态表和列名”。

示例如下。您可以改进这一点，以确保源列和目标列具有兼容的类型，或者排除身份列或计算列，但它应该给您一个想法。

DECLARE @SourceTable sysname
DECLARE @DestTable sysname

SET @SourceTable = '[dbo].[#TempTable]'
SET @DestTable = '[dbo].[OtherTable]'


DECLARE @DynSQL1 NVARCHAR(MAX)
DECLARE @DynSQL2 NVARCHAR(MAX)


SELECT 
@DynSQL1 = ISNULL(@DynSQL1 + ',','') + QUOTENAME(sc1.name),  
@DynSQL2 = ISNULL(@DynSQL2 + ',','') + QUOTENAME(sc2.name)
FROM tempdb..syscolumns sc1
JOIN syscolumns sc2 
  ON sc1.colorder = sc2.colorder /*Match up the columns by column order*/
WHERE sc1.id=OBJECT_ID('tempdb.' + @SourceTable) AND sc2.id=OBJECT_ID(@DestTable)

IF @@ROWCOUNT = 0
  RETURN


SET @DynSQL1 = 'INSERT INTO ' + @DestTable + ' (' + @DynSQL2 + ')
  SELECT ' + @DynSQL1 + ' FROM '+ @SourceTable +';'

EXEC sp_executesql @DynSQL1

Answer 3

您可以指定目标表的列：

INSERT INTO OtherTable (FirstName, LastName, Region1, Region2)
  SELECT FirstName, LastName, Column1, Column2 FROM TempTable;

在此示例中，OtherTable.Region3 将最终为 NULL（或者如果它有 DEFAULT 值，它将使用该值）。

INSERT 中的列数必须与SELECT 中的列数匹配。因此，您必须知道 TempTable 中有多少列，并使插入的列列表匹配。

但是，如果您只是乱扔 SELECT * ，则无法使用隐式列来实现此目的。

---

回复您的评论：您可以使用 INFORMATION_SCHEMA 查询表中的列数和列名。

SELECT COLUMN_NAME
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'tempdb' AND TABLE_SCHEMA = 'MySchema'
  AND TABLE_NAME LIKE 'MyTempTable%';

然后，您将编写应用程序代码来根据此 information_schema 查询的结果创建 SQL INSERT...SELECT 语句。

注意：通过 information_schema 查询临时表需要 特殊处理。