Select entire row into another table regardless of column names in SQL Server

Question

Is there a way to select the row from a temp table (table has only one row anyway), into another table that has some columns with differenet names? For example:

TempTable

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

OtherTable

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

I need to copy the data, in the same order as the columns from TempTable into OtherTable. TempTable will not always be the same....as in sometimes it will have 3 columns, sometimes just 2...etc. If it does not have the same number of columns as OtherTable, the the remaining "Region" columns should stay null.

The end result should be:

OtherTable

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

PLUS the column names in TEMPTable will NEVER be the same...as in one time it will be "Column1"...the next time it could be "XXXXX1". That's why I just want to copy data only...the data will always be in the correct order...

LOL...does this even make sense? This is for SQL Server 2005

Answer 1

EDIT ........ Dynamic SQL Generation added

This code will generate INSERT statements to INSERT from #TEMP into #TEMP. You can tweak it to suit your purpose if you are going from #temp to regular tables.

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')

---

---

---

OLD ANSWER

Yes you can do this but you have to write different statements for each type of INSERT. You do have to specify column names in both places - the INSERT INTO and the SELECT

---

If you have the same number of columns in your Source and Destination tables, do this

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

What this will do is map as follows:

MyTable.MyColumn01 -> Table1.Column1 MyTable.MyColumn02 -> Table1.Column2 MyTable.MyColumn03 -> Table1.Column3

---

If the Source has less columns, you can use a NULL value in place of the column name

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

OR you can just use two column names

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

---

If the destination table has less columns than the source, then you have to ignore columns from the source

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

Answer 2

You can specify the columns of the target table:

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

In this example, OtherTable.Region3 will end up NULL (or if it has a DEFAULT value, it'll use that).

The count of columns in the INSERT must match the count of columns in the SELECT. So you must know how many columns in TempTable and make the list of columns for the insert match.

But there's no way to do it with implicit columns, if you're just throwing SELECT * around.

---

Re your comment: You can use the INFORMATION_SCHEMA to query the count and the names of the columns in the table.

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

Then you would write application code to create your SQL INSERT...SELECT statement based on the results from this information_schema query.

Note: Querying temp tables through the information_schema requires special handling.

Answer 3

You could do something with dynamic SQL.

I recommend reading "The Curse and Blessings of Dynamic SQL - Dealing with Dynamic Table and Column Names" if this is new to you.

Example follows. You could improve this to be sure that source and destination columns are of compatible types or to exclude identity or computed columns for example but it should give you an idea.

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