How do you create a new table with columns from a dozen different tables-CodePudding

I'm trying to help update some code for a non-profit I volunteer with. Someone wrote them a SQL script years ago and it needs updating to work with new tables in their database. I've been trying to follow the patterns in the existing code to add the new tables and the necessary columns from the new table to this script and I can't figure it out. I've been working on this for over a week. Can someone please take a look and please help?

The part of the script I need help with is

---CREATE Starting Table
SELECT
    [Current Year],
    [Agency ID],
    [App Number],
    [Client Number],
    [Last Name],
    [First Name]
INTO 
    ClientToProcess
FROM 
    ClientTBLInfo as m;

This script was grabbing information from the columns:

[Current Year]
[Agency ID]
[App Number]
[Client Number]
[Last Name]
[First Name]

which are located in just one table - ClientTBLInfo.

Over time they've added new tables and columns to the database.

So now there are about 12 more columns across about 8 different tables beyond the ClientTBLInfo table that needs to be added to this script and I can't figure out how to add these new tables to the script.

I appended the SELECT statement by adding the new column names to the list. However, I don't know how to append this part of the script where I tell it which tables to look for the new columns in.

INTO ClientToProcess
FROM ClientTBLInfo as m;

There's literally like a column from one table that needs to be added and then two columns from a different table, etc.

Any suggestions on how to do this?

Thank you in advance.

CodePudding user response：

In order to keep it flexible, I would build a dynamic SQL, something like this:

declare @cmd varchar(max) = 'SELECT ';
        
select @cmd  = STRING_AGG(TableName   '.'   ColumnName, ', ')
  from ClientTBLInfo;
        
set @cmd  = ' FROM (SELECT NULL as DummyColumn) AS DummyTable JOIN ';
        
select @cmd  = STRING_AGG(a.TableName   ' ON xxx ', ' JOIN ')
  from (select distinct TableName from ClientTBLInfo) a;

execute (@cmd);

You would have to figure out the JOIN conditions or maybe use a CROSS JOIN. The current result is:

SELECT Table1.Col1, Table2.Col1, Table1.Col2 FROM (SELECT NULL as DummyColumn) AS DummyTable JOIN Table1 ON xxx  JOIN Table2 ON xxx

CodePudding user response：

First, we go back to the original statement. There you see the m, which tells This column is from table m. Table m is the table ClientTBLInfo (renamed to m with as m).

SELECT
    m.curr_year,
    m.agencyId,
    m.app_number,
    m.client_number,
    m.lastname,
    m.firstname
INTO 
    ClientToProcess
FROM 
    ClientTBLInfo as m;

First, we rename the table to a more meaningful name and we add the other tables you need with a (here inner) JOIN:

SELECT clientInfo.[different columns],
    agency.name,
    agency.numberOfEmployees
FROM ClientToProcess as clientInfo
    INNER JOIN Agency ON agency.id = clientInfo.agencyId;

We joined the other table with a relationship, in this case the columns agencyId from ClientToProcess and id from Agency. This is called an INNER JOIN because it requires the records to be joined to be in both tables. If there's no record for a given agencyId in Agency, there'll be no result row for that agencyId.

Here you can read about joins, and which one you need: SQL Server Joins - A Visual Explanation

That way, you can join multiple tables together and get all the columns you need.

As you select your results in a variable called ClientToProcess, you probably need to update that one as well. Look up its definition and add your new columns there, too.