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 SorrellTry 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...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"Ron Sorrell" <ron.sorrell@.quaysys.com> wrote in message
news:4cgrmv0tnu355pk28jb7si938uq9pe6d85@.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|||Perfect
Thank you very much
Ron Sorrell
On Sun, 21 Sep 2003 20:04:31 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:
>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 InsertStatementssqlsql
