Try something like:
SET NOCOUNT ON
CREATE TABLE ALLCPY
(
machineid int NOT NULL,
name varchar(30) NOT NULL
)
DECLARE @InsertStatement nvarchar(4000)
DECLARE InsertStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'INSERT INTO ALLCPY
SELECT machineid,name FROM ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME LIKE '[_]CPY%'
OPEN InsertStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InsertStatements INTO @InsertStatement
IF @@FETCH_STATUS = -1 BREAK
EXEC(@InsertStatement)
END
CLOSE InsertStatements
DEALLOCATE InsertStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index....partmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq
-----------------------
"Ron Sorrell" <ro*********@quaysys.com> wrote in message
news:4c********************************@4ax.com...
I have a requirment to take data from a large set of tables where the
total number of these tables may change on a regular baisis and
output into another table. All the tables willl have the same
columns. Frequency is being debated but it maybe as much as once per
hour.
Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.
2) then I need the following
for each of the tables found above, I need the outfrom from each of
those tables to be inputted into another table. basically, I would
want the following output from each of the tables found in step 1
select machineid,name from _cpy_offermanager_678
3) In the end I would have something like dbo.ALLCPY with records
combined from all other _CPY tables
Ron Sorrell
