Extract data from many tables

Transact-SQL
1

Hi There,

I need your help please.
I need to extract data from many tables. This is a brief run down.
Get all records from table1 and for each record found look for related records in table2 so the data exported will look like.

Table1 Details
Table2 Details
Table2 details
Table1 Details
Table2 Details
Table2 details
Table2 Details
Table2 details
etc

Could someone let me know how I do that.

Thank you.

Best Regards,

Steve.

2

select * from table1
join table2 on table1.? = table2.?

where ? indicates the column used to find related rows (preferred name over "records")

3

Are you wanting to export to another table or to a (text) file?

4

Hi Scott,
Thanks for the reply.

Ultimately it will be exported to a .csv file.

Best Regards,

Steve.

5

That's actually easier, since you can concatenate everything to a single text value. Once you get the code below validated, you can use that as the base query for an Export from SSMS or for bcp if you want to use only T-SQL. Naturally the "TOP (10)" are just to allow for quick testing.

SELECT concat_columns
FROM (
    SELECT TOP (10) 1 AS table_code, t1.key_col, <code_to_concat_all_table1_columns> AS concat_columns
    FROM dbo.table1 t1
    UNION ALL
    SELECT TOP (10) 2 AS table_code, t2.key_col, <code_to_concat_all_table2_columns> AS concat_columns
    FROM dbo.table2 t2
) AS derived
ORDER BY table_code, key_col
6

It could be like:

SELECT table1.column1+','+p.column2+','+p.column3 as column_A, table2.column1+', '+table2.column2 AS column_B from table_A as table1 inner join table_B as table2 on table1.column=table2.column