Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

HOw to add table name to insert result wiht sp_foreachtable

Avatar of Anthony Cardullo
Anthony CardulloFlag for United States of America asked on
Microsoft SQL Server 2008
2 Comments1 Solution489 ViewsLast Modified:
I am using the sp_Foreachtable method to grab records from a group of tables and insert them into an existing table in same db. What i would like to do is add the table name of the record in which it was found.  i can add the tablename field to the exisitn table sprintdb.dbo.EbtSprintLookup to accomodate the added tablename value.


This is what i have now:


declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @RETURN_VALUE varchar(255)

set @mycommand = 'select * from ? where Field9 = ''9995554323'' and Field1 = ''030'''
set @mywhereand =  'and o.name like ''EbtSprint2%'''

insert INTO sprintdb.dbo.EbtSprintLookup

exec @RETURN_VALUE=sp_MSforeachtable
               @command1 = @mycommand,
              @whereand = @mywhereand
             select *
             from sprintdb.dbo.EbtSprintLookup
             order by abs(field3)
            truncate table sprintdb.dbo.EbtSprintLookup
Avatar of chubby_informer
chubby_informerFlag of Trinidad and Tobago image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers