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

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

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros