How to create a stored procedure which pulls data from multiple tables that are not linked What do i do

Question

I need to create a stored procedure which pulls data from multiple tables that are not linked what do i do .Basically i got a few tables that are not linked and all i need to do is pull all the data from all those tables.

PLEASE HELP MY LIFE DEPENDS ON THIS

Answer 1

Just do several selects?

select * from MyTable1;
select * from MyTable2;
select * from MyTable3;

You can then access each of those tables from your calling code. I'd include an example on how to do that, but you don't include any details about what language you are calling from.. details I would have included if my life depended on it :)

If you mean you have rows in several tables, and you want to pull them all back then you can do that by doing a UNION.

Answer 2

What do you mean by 'not linked'? Are they on the same sql server instance? If so, just select from the full schema name.

Eg.

Select * from database1.dbo.table Select * from database2.dbo.table

Answer 3

If you want a single result set you can use the JOIN keyword whether or not the tables have foreign key relationships defined; just specify which columns you would like to join the tables on.

Alternatively, you can use multiple result sets and just have multiple SELECT statements.

Answer 4

Your questions is not clear, but if you are trying to pull just certain things from certain tables and then return them to the user, can do something like this:

CREATE PROCEDURE spMyTestProc 
AS

        DECLARE @F1 int
        DECLARE @F2 int
        DECLARE @F3 int
        DECLARE @f4 char(10)

        SELECT @f1 = FIELD1 from MYTABLE1

        SELECT @F2 = FIELD2 FROM MYTABLE2

        SELECT @F3 = FIELD3, @F4=FIELD4 FROM MYTABLE3


    /* NOW return the result set to the user, it'll come back just like a regular select */    
        SELECT @F1 AS F1, @F2 AS F2, @F3 AS F3, @F4=F4

Answer 5

The presence or absence of primary or foreign keys doesn't prevent you from joining the tables. (It won't be particularly fast, but you can do it.) Neither does their existing in separate database instances.

You say that your databases aren't linked. From this, I have to infer that the database you're writing the stored procedure in likely has no access to the 2nd database; if that's the case, you won't be able to access it anyway. You need to add a link to the 2nd database using Enterprise Manager.

Once you've done that, you can reference the tables in the 2nd database using its schema name as below:

SELECT *
FROM foo.employees

where foo is the schema name.

Good luck.

Answer 6

SELECT Field1, Field2, Field3 FROM Table1 
UNION ALL
SELECT F1, F2, F3 FROM Table2 
UNION ALL 
SELECT Fld1, Fld2, Fld3 FROM Table3;

This will pull all data from all three tables into single output. Check that other table fields match type of the first table and you are good to go.

Answer 7

So I have a similar question....

When running a select statement select field1, field2, field3, field4 from table1

The values for field2 and field3 are a "code"...

The value of the code in field2 is in table2, and the value for the code in field3 is in table3

Example: Field2=16 , In the employee table, There is a column employee_id. Where employee_id=16, first_name_x= Jane , Last_name_x= Doe

So instead of having the value for field2=16, Id rather the output be Doe, Jane

Another Example: Field3=AA ; In the table code_category, There is a column code_c. Where code_c=AA , description_x= Available for Adoption

So instead of having the value for field3=AA, Id rather the output be Available for Adoption

So, I would like to run a select statement that pulls up the values for field1-4... but substitutes the codes in field2&3 for the description....

I hope you follow, sorry if I made this sound more confusing than it needed to be.