Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop and put contents in Temp Table 1

Status
Not open for further replies.
robertfah

robertfah

Programmer
Mar 20, 2006
380
US
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:

Code: 
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:

Code: 
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.
 
Sort by date Sort by votes
Code: 
DECLARE @users TABLE (define fields exactly
                      as they are in USERS tables)

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 *
            FROM ' + @DBName + '.dbo.Users'

        INSERT INT) @Users        
        exec(@s)
    
        FETCH NEXT FROM LoopCursor
        INTO @DBName
    END

    CLOSE LoopCursor
    DEALLOCATE LoopCursor
    SELECT * FROM @Users
(not tested)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
DECLARE @users TABLE (define fields exactly
as they are in USERS tables)
Click to expand...

My bad....I guess I should have stated that I got it to work this way but was looking for a way around it. Basically, I didn't want to have to modify this SQL once I got it complete no matter how many fields are added or removed from the table (not that it happens alot).
 
Upvote 0 Downvote
When fields are added or removed, are they done for all databases or could the table structure be different for each one?

-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
 
Upvote 0 Downvote
When fields are added or removed, are they done for all databases or could the table structure be different for each one?
Click to expand...

They would be the same, for all the DB's.

Sorry I haven't replied sooner but been busy in meetings.
 
Upvote 0 Downvote
In that case, you can use a temp table instead of a table variable.

Ex:

Code: 
Select * 
Into   #Users
From   DatabaseName.dbo.TableName
Where  1=0

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 *
            FROM ' + @DBName + '.dbo.Users'

        INSERT INT0 #Users        
        exec(@s)
    
        FETCH NEXT FROM LoopCursor
        INTO @DBName
    END

    CLOSE LoopCursor
    DEALLOCATE LoopCursor
    SELECT * 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
 
Upvote 0 Downvote
Thanks George...can you explain this part to me:
Select *
Into #Users
From DatabaseName.dbo.TableName
Where 1=0
Click to expand...

I know it's creating a temp table called #Users but not too sure what I'm supposed to do with the rest.
 
Upvote 0 Downvote
You just need to replace DatabaseName.dbo.TableName with your actual Users table from whatever database you want.
This just creates an empty temp table with the same structure as Users table.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Thanks Boris. I couldn't have said it better myself.

-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
 
Upvote 0 Downvote
Sorry George,
I thought it is very early morning in your part of the world :)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Ok, I thought that's what it was. Thanks for all the help thus far but I think something is missing. Let me try to explain this a little better. I have 35 DB...all are identical...same tables, same fields, everything (our customers require separate DB's by contract). We also have our own 'master' db (SCMaster) which holds the DB names (amongst other things) so we can get a list of all of our customers fairly easily.

We have a Ticket Tracking system that I want to pull a list of ALL the users for ALL of our customers. To do this, I would have to go to each DB and do the standard 'Select * from Users' and combine my results....not effecient. I'm trying to use a single SQL statement that get a list of the DB names from our master table (SCMaster) and gets the contents of each Users table and puts them in a temp table so I can return that to our program.

So something like:
Code: 
For each DBName in SCMaster.dbo.Configurations
  Select INTO #TempUsers
  FROM DBName.dbo.Users

Next

Select * from #TempUsers
Totally not meant to be SQL Code....more of a hybrid of C#/SQL, etc, lol. Just trying to explain it better.
 
Upvote 0 Downvote
Select *
Into #Users
From DatabaseName.dbo.TableName
Where 1=0
Click to expand...
Is that just to get the initial table definition? I think I'm starting to see how this works.
 
Upvote 0 Downvote
ok, so disregard 2 posts ago....I understand it now....sorry, just wasn't thinking clearly.

I changed a few syntax errors and run it, but I get this:
Code: 
(0 row(s) affected)

Server: Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

So what I did was narrow the SQL in the loop to specifically select only 1 DB and tried it again, but got the same error. I then commented out the whole loop and made sure the temp table was being created correctly and it is. and then I matched those columns up to the columns in the specific DB and they are all the same. So maybe the error isn't the correct error but something else is going on...

Modified SQL:
Code: 
Select *
Into   #Users
From   SC_ASU.dbo.Users
Where  1=0

GO

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')
	WHERE DBName = 'ASU'

    ORDER BY 'SC_' + c.DBName

    OPEN LoopCursor

    FETCH NEXT FROM LoopCursor
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

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

        INSERT INTO #Users        
        exec(@s)
    
        FETCH NEXT FROM LoopCursor
        INTO @DBName
    END

    CLOSE LoopCursor
    DEALLOCATE LoopCursor

SELECT * FROM #Users
 
Upvote 0 Downvote
try removing the GO.

-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
 
Upvote 0 Downvote
After doing some research, it sounds like I have to speficy the columns when using the INSERT INTO statement. Don't know how I'm going to get around this.....any ideas?
 
Upvote 0 Downvote
You would only need to specify the columns during an insert into if the columns don't match. In fact, it's important that the ordinal position of the columns match too.

try this code:

Code: 
Select	Convert(VarChar(100), '') As DatabaseName, *
Into	#Users
From	SC_ASU.dbo.Users
Where	1=0

exec sp_msforeachdb '
	If Exists(select 1 From [?].Information_Schema.Tables Where Table_Name = ''Users'') 
	    Insert 
	    Into   #Users 
	    Select ''?'',* 
	    From   [?].dbo.Users'

Select * From #Users
Drop Table #Users

If you get the same error, then it must be that the tables are different between your databases.

-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
 
Upvote 0 Downvote
try removing the GO.
Click to expand...

Same error.....it sppears to have something to do with the Identity Seed (I'm guessing) on the #Users table because if I specify 1 column (FirstName) and use that column in the loop, I get data. The minute I try to add UserID (the PK auto-incrementing column), I get the error:
Code: 
(0 row(s) affected)

Server: Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

So this works:
Code: 
Select FirstName
Into   #Users
From   SC_ASU.dbo.Users
Where  1=0

--code omitted

        SET @s = '
            SELECT FirstName
            FROM ' + @DBName + '.dbo.Users'

        INSERT INTO #Users 
        EXEC(@s)

But this doesn't:
Code: 
Select UserID, FirstName
Into   #Users
From   SC_ASU.dbo.Users
Where  1=0

--code omitted

        SET @s = '
            SELECT UserID, FirstName
            FROM ' + @DBName + '.dbo.Users'

        INSERT INTO #Users 
        EXEC(@s)

Thoughts?
 
Upvote 0 Downvote
Do you have any DB's with spaces in the name?

You should change this:

[tt][blue]
SET @s = '
SELECT UserID, FirstName
FROM ' + @DBName + '.dbo.Users'

INSERT INTO #Users
EXEC(@s)
[/blue][/tt]

to this:
Code: 
        SET @s = '
            SELECT UserID, FirstName
            FROM [!][[/!]' + @DBName + '[!]][/!].dbo.Users'

        INSERT INTO #Users 
        EXEC(@s)

-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
 
Upvote 0 Downvote
You would only need to specify the columns during an insert into if the columns don't match. In fact, it's important that the ordinal position of the columns match too.

try this code:


Code: 
Select    Convert(VarChar(100), '') As DatabaseName, *
Into    #Users
From    SC_ASU.dbo.Users
Where    1=0

exec sp_msforeachdb '
    If Exists(select 1 From [?].Information_Schema.Tables Where Table_Name = ''Users'') 
        Insert 
        Into   #Users 
        Select ''?'',* 
        From   [?].dbo.Users'

Select * From #Users
Drop Table #Users
If you get the same error, then it must be that the tables are different between your databases.
Click to expand...

Ok, I ran this in a new query window and got the following errors:

Code: 
(0 row(s) affected)

Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 8101, Level 16, State 1, Line 3
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.
Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition.

So does this mean that there are 5 (out of 57) DB's that have a Users table with the different columns? This could be true for the reason that we do have other DB's with a table called Users...but those DB's are being excluded, which is why I get a list of only the DBs that are identical from our SCMaster.configuration table:
Code: 
  SELECT 'SC_' + DBName AS 'DBName'
           FROM [SCMaster].dbo.Configuration c
    WHERE DBName NOT IN ('TIER1', 'FAAO')
    ORDER BY 'SC_' + c.DBName


But for s&g's I wanted to check manually, so ran the above query by itself and it returned the DBNames in the order in which the loop would use them. Seeing as how in that BIG list of errors (when I ran the code you gave me), the first error was the columns not matching, I took that DB (it should be the first one in my list) and opened the Users table and compared it to SC_ASU.dbo.Users table inside Enterprise Manager. All the columns are named the same (see attachment).
 
 http://i979.photobucket.com/albums/ae271/robertfah/Code%20Junk/Capture.png
Upvote 0 Downvote
Do you have any DB's with spaces in the name?
Click to expand...
No....all DB names are SC_ and then a unique customer code...ex:

SC_ASU
SC_CAGTCG
SC_24THSBB

Thanks for all your patience and work on this....it's a mind-boggling thing for me, but I'm learning a lot!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
369
BobSuruncle
BobSuruncle
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
236
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
246
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
318
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
294
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top