Hi,

Currently I got 1 main table (Table01)
and 4 associate tables which are Table01ABC, Table01ACD, Table01HSD,
Table01HGG, and these 4 table names are named accordingly the data in
Table01

the data in Table01 is
ABC
ACD
HSD
HGG

Main Table (Table01) which stored part of the associate table names.
the associate tables increase, and the data in Table01 will increase
accordingly as well...

I want to do something to LOOP the main table (Table01) first......

then inside the looping, write a select statement which will merge all
associate table (Table01ABC, Table01ACD, Table01HSD, and TableHGG)
together.....

How to create the procedure to run the above inside SQL ?

..........
Pls note that associate table naming will be dynamic, the only way to know
which assoicate to pull out is to loop the main table (table01) first. So
after, looping the Main Tables(Table01), then we only know the associate
tables name. then we only can run a select statement to merge all associate
tables together.....

Pls help me to create a procedure for the above requirement

Thank you very much

regards

PK

the characters is choose by system.....so that would be a unique key....and
3 bit only...

merge the tables....i simply mean query all associate tables and insert into
a new view or table....
after the query.....all the data in the associate tables will be insert into
a new table or view...


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

PK
Sorry, in my opinion it is a mess.
What will be happened if one of the users create a table called Tableheheh?
Who is responsible to choose a charcters for the table? What do you mean by
'merge the tables'?

How does above phrase make a sense ?





" PK" <pk999 (AT) hotmail (DOT) com> wrote

PK
Let me understand it.
Ok , a system is choosen for instance 'FGH' so you have to check whether or
not this assotation exists within a main table? Am I right?
IF NOT EXISTS (SELECT * FROM Main WHERE col='FGH' )
BEGIN
END
AS
SELECT col....FROM TableABC
UNION ALL
SELECT col....FROM TableDEF

Not clear to me what are you trying to do?
SELECT * INTO NewTable FROM my_view





" PK" <pk999 (AT) hotmail (DOT) com> wrote

Rather crude but may give you some ideas:

Create a cursor (or select into a temp table) by selecting
the table name from Table01, this will give you a list of
all the Table01xxx names.

(Select 'Table01' + [t01.ColumnName] as tblName
INTO ##temp_table From Table01 t01)

See BOL for Cursor syntax.

Loop through the cursor or temp table (with a cursor) and
build a dynamic sql select statement with the union
operator to produce a single results set.

You could also use vbscript in DTS to create a recordset.

YES....that's all what i want to do....

how to create a DTS package and do all the thing ?

or how could I write a stored procedure to do all the thing


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

now...i am facing the problem is the multiple select statement inside the
looping....
pls see my code....
i am using tnames_cursor to identifie the tablename...
then i loop the total number of rows of Main Table ( Table01) in order to
get the assicoate table name...
when in rows 1, then read 1st associate table name, then will run a select
statement to query all data in 1st associate table....
when in rows 2, then read 2st associate table name, then will run a select
statement to query all data in 2st associate table....
when in rows 3, then read 3st associate table name, then will run a select
statement to query all data in 3st associate table....

so my output is.....i run a total of 3 select statement, but was unable to
union join all 3 table......cos my table is loop one by one,
so i am having difficulites to join the table...

so i need help on that part.....

i attached my coding here for refernece......
1st part is written on Query Analyzer...
2nd part is a Stored Procedure.....

---run on Query Analyzer....

DECLARE tnames_cursor CURSOR
FOR
SELECT CompanyCode
FROM scacompanies
OPEN tnames_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tnames_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
EXEC Merge_Aging @tablename
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor


...stored Proceudre "Merge_Aging" is here...
----START stored procedure

CREATE PROCEDURE Merge_Aging @CC char(2)
AS
create table #Temp_1 ( compname varchar(255),compcode varchar(10) ) insert
into #Temp_1
select Companyname,CompanyCode from ScaCompanies where CompanyCode = @CC
EXECUTE ( 'SELECT SL01001, SL01002, SL03002, SL03003, SL03004, compname,
compcode FROM SL01'+@CC+'00 , SL03'+@CC+'00, SYCD'+@CC+'00, #Temp_1 WHERE
SL03001 = SL01001 and SL03015=SYCD001 ')
drop table #Temp_1
GO
----END stored procedure


----------------------------------------------------------------------------
--------------------------
"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

PK
Create a stored procedure with all those things we are talked about . If you
want it as an one transaction wrap it into BEGIN TRAN ......COMMIT



" PK" <pk999 (AT) hotmail (DOT) com> wrote

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

5...so in order to know what associate tables are available, one must loop
the Main Table (Table01), in order to know how many assoicate tables are
available.....if got 5 rows, that mean got 5 assocaite tables...

so must loop MainTable (Table01)
Begin
Select * from Table01XXX
Fetch next rows (XXX)
End

if in that case, how could i join all the table ? using append to upate all
date to a view or table ?

PK
Please post DDL + sample data + expected result
It is hard test without actual data




" PK" <pk999 (AT) hotmail (DOT) com> wrote