Anthony Cardullo
asked on
HOw to add table name to insert result wiht sp_foreachtable
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.EbtSprintLook up to accomodate the added tablename value.
THanks
This is what i have now:
USE SPRINTDB
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.EbtSprintLook up
exec @RETURN_VALUE=sp_MSforeach table
@command1 = @mycommand,
@whereand = @mywhereand
select *
from sprintdb.dbo.EbtSprintLook up
order by abs(field3)
truncate table sprintdb.dbo.EbtSprintLook up
THanks
This is what i have now:
USE SPRINTDB
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.EbtSprintLook
exec @RETURN_VALUE=sp_MSforeach
@command1 = @mycommand,
@whereand = @mywhereand
select *
from sprintdb.dbo.EbtSprintLook
order by abs(field3)
truncate table sprintdb.dbo.EbtSprintLook
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER