Loop and put contents in Temp Table 1

[robertfah]

I've got some SQL that I'm trying to create and need some help. I have many (35+) DB's that are identical in nature but hold different content. I am trying to create a SQL statement that will go through each DB and select the contents from the 'Users' table and put them in a temp table. Then, once it's done, select the contents of that temp table. Here's what I've got so far:

DECLARE	@DBName varchar(20)
DECLARE @s varchar(2000)

DECLARE LoopCursor CURSOR FOR
	SELECT 'SC_' + DBName AS 'DBName' FROM [SCMaster].dbo.Configuration c
	WHERE DBName NOT IN ('TIER1', 'FAAO')
	ORDER BY 'SC_' + c.DBName

	OPEN LoopCursor

	FETCH NEXT FROM LoopCursor
	INTO @DBName

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @s = '
			SELECT *
			INTO #TempTable
			FROM ' + @DBName + '.dbo.Users'
		
		exec(@s)
	
		FETCH NEXT FROM LoopCursor
		INTO @DBName
	END


	CLOSE LoopCursor
	DEALLOCATE LoopCursor

GO

Select * from #TempTable

The Messages window shows all the rows being affected but at the end it states this error:

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '#TempTable'.

I'm assuming it's because the table is no longer available in memory....but how can I accomplish this?

BTW, the first SELECT statement is selecting all my DB's that I have stored in a table.

 

[robertfah]

I may be way off on this but I still believe the wrong syntax or something is being used because I took it down to the most basic SQL possible....create a temp table from an existing table and then insert records:

IF object_id('tempdb..#Users') IS NOT NULL
	DROP TABLE #Users

Select UserID, FirstName
Into   #Users
From   SC_ASU.dbo.Users
Where  1=0

INSERT INTO #Users
SELECT UserID, FirstName
FROM Users

When I run it, I get the error:

Server: Msg 8101, Level 16, State 1, Line 9
An explicit value for the identity column in table '#Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried to turn Identity_Insert on and then off again but I still get the same error. ??????????????????

 

[gmmastros]

Well, I guess the problem is with the Identity column. I just found a hack that may work, but it seems a bit brittle to me. When you insert into a table without specifying the columns, you need to make sure the tables have identical structure, including ordinal values (column 1 in Table A is the same as column 1 in Table B).

As such, perhaps you can try this.

IF object_id('tempdb..#Users') IS NOT NULL
    DROP TABLE #Users

Select 1 As TempColumn, UserID, FirstName
Into   #Users
From   SC_ASU.dbo.Users
Where  1=0

[blue]Alter Table #Users Drop Column UserId
TempDB..sp_rename '#Users.TempColumn','UserId', 'COLUMN'
[/blue]

INSERT INTO #Users
SELECT UserID, FirstName
FROM Users

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks a bunch George....that solved the problem. I was already knee-deep into writing another SP that would get the DBNames, then get the column names for those (through sysobjects) and then create a literal string and use that to create the temp table and the insert statements....but my head started to hurt, lol.

Here's the final product:

IF object_id('tempdb..#Users') IS NOT NULL
	DROP TABLE #Users

Select 1 AS TempColumn, *
Into   #Users
From   SC_ASA.dbo.Users
Where  1=0

Alter Table #Users Drop Column UserId
GO
tempdb..sp_rename '#Users.TempColumn','UserId', 'COLUMN'

DECLARE @DBName varchar(20)
DECLARE @s      varchar(2000)

DECLARE LoopCursor CURSOR FOR
    SELECT 'SC_' + DBName AS 'DBName'
    FROM [SCMaster].dbo.Configuration c
    WHERE DBName NOT IN ('TIER1', 'FAAO')
    ORDER BY 'SC_' + c.DBName

    OPEN LoopCursor

    FETCH NEXT FROM LoopCursor
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @s = '
            SELECT u.*
            FROM [' + @DBName + '].dbo.Users'

        INSERT INTO #Users 
        EXEC(@s)

        FETCH NEXT FROM LoopCursor
        INTO @DBName
    END

    CLOSE LoopCursor
    DEALLOCATE LoopCursor

SELECT * FROM #Users
Order BY FirstName

Thanks again, much appreciated!