A Function to retrieve data from more then one Tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A Function to retrieve data from more then one Tab

Author

Topic

Sedanda
Starting Member

13 Posts

Posted - 2010-09-08 : 09:37:11

Hi Forum,
I need a funtion to retrieve data from more then one Table. The tables are not related.Field names are the same in all tables. The table list I get from a view and i need to go through this list and pull out any records from the different tables that have a true value in a certain field. I am new to SQL and would appreciate any help. I am using SQL 2000

Thanks
Sedanda

Sedanda
Starting Member

13 Posts

Posted - 2010-09-09 : 01:54:29

Hi Forum,
No tips or ideas how to do this? I would be grateful for any Ideas on how to do it.

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 02:59:27

Some sample data and table structure would help.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sedanda
Starting Member

13 Posts

Posted - 2010-09-09 : 03:17:04

Ok I Built a cursor that retrieves all the Table names from my Table

GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @Table varchar(50);

DECLARE Table_cursor CURSOR FOR
SELECT Projekt FROM C_TFLS_Status_Matrix
WHERE FLSExportAktiv = -1
ORDER BY Projekt;

OPEN Table_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 
FETCH NEXT FROM Table_cursor
INTO @Table;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Concatenate and display the current values in the variables.
   PRINT @Table
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM Table_cursor
   INTO @Table;
END

CLOSE Table_cursor;
DEALLOCATE Table_cursor;
GO

now I have to use this cursor as a parameter in the Funktion.

Field names that I have to retrieve are [CNI],[CNE],[CNT]
from each table in the list @Table where [ANT] = True
Any ideas on how to do this
Thanks
Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-09 : 03:18:01

sounds like you want a UNION of 2 tables


select <column list>
from   table1
where  somecol = 1
union
select <column list>
from   table2
where  somecol = 1

KH
[spoiler]Time is always against us[/spoiler]

Sedanda
Starting Member

13 Posts

Posted - 2010-09-09 : 03:30:30

I have this as a View where I have a Union between all the tables but it is a performance killer and takes hours to run. There are so many tables and the view is not fast enough and we have to edit the view every time we need to get the data from a new table . If I had a function that takes the Table name from a list it would save time and a lot of work every time a have a new table.

Sedanda
Starting Member

13 Posts

Posted - 2010-09-09 : 06:02:20

My Sp retrieves all the names of the tables that I need
I would like to use my SP dynamically in a loop with a union select command
But the problem is that I dont know where to start.

Has anyone an idea how to do this? and please explain it for beginners.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-09 : 07:54:30

sample data and output ?

Sedanda
Starting Member

13 Posts

Posted - 2010-09-09 : 08:20:16

Hi sakets_2000
What exactly do you need from me? How can I give you sample data or output? Please explain.
Jeff

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-09 : 13:53:41

>> I need a function to retrieve data from more then one Table. The tables are not related. Field [sic:columns are not fields] names are the same in all tables. <<

That is a description of magnetic tape files and not an RDBMS. Tape files all have the same record structure, so they can be processed sequentially. There should be one and only one table in the schema for each set of entities in the data model, not multiple ones. Files have fields, which are nothing like columns in a table.

>> The table list I get from a view and I need to go through this list and pull out any records [sic: rows are not records] from the different tables that have a TRUE [RDBMS uses predicates and not Boolean flags] value in a certain field [sic]. <<

Yes, you have written a 1950's magnetic tape file system using SQL. We use set processing and not sequential processing like a magnetic tape file.

>> I am new to SQL and would appreciate any help. <<

Start with the basics and read a book. You don't yet know the right words or concepts for RDBMS. You will kludge together a nightmare with UNIONs and CURSORs unless you stop programming right now.

You need more help than you can get here; find an experienced SQL programmer and ask for help and some book recommendations.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources