T-SQL to merge columns from two tables into one and join

Question

I am very new to T-SQL coding (so apologies in advance that I couldn't figure this out). I created a new table and I am pulling in column info from two different tables to consolidate it. The 'Select' to the 'From' creates the columns (no data pulled in though, when I use just one table (C1) data is pulled into the columns) and I have used aliases on the tables but I am obviously not setting up the 'Join' statement correctly to be able to pull the data into the new table. I have verified all the tables names, etc. so I am sure it is the way I am writing the script itself. Any help would be appreciated to show me what I am doing incorrectly (using SQL Server 2012):

INSERT INTO view_invfu
    SELECT
        C1.claimNo AS invfu_history_claimNo,
        C2.ClmHistoryId AS invfu_history_id,
        CAST(C1.Date AS datetime) + CAST(C1.Time AS datetime) AS invfu_DateTime,
        C1.priority AS invfu_priority,
        C1.status AS invfu_status,
        C1.Date AS invfu_date,
        C1.Time AS invfu_time,
        C1.assignedById AS invfu_assignedById,
        C1.assignedToId AS invfu_assignedtoId,
        C1.isactive AS invfu_active,
        C2.actionId AS invfu_actionId
    FROM
        claim_assignedto_history C1, claim_assignedto_historydetail C2
    LEFT OUTER JOIN 
        C2 ON C1.ClaimNo = C2.ClmHistoryId
    ORDER BY 
        1 ASC

Thanks in advance!

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, May 05 '18 at 14:28
alter your query like this.. `FROM claim_assignedto_history C1 LEFT OUTER JOIN claim_assignedto_historydetail C2 ON C1.ClaimNo = C2.ClmHistoryId`... else follow **D T** — Pugal, May 05 '18 at 15:22

score 0 · Accepted Answer · answered May 05 '18 at 13:15

0

Try this.

SELECT
C1.claimNo AS invfu_history_claimNo,
C2.ClmHistoryId AS invfu_history_id,
CAST(C1.Date AS datetime) + CAST(C1.Time AS datetime) AS invfu_DateTime,
C1.priority AS invfu_priority,
C1.status AS invfu_status,
C1.Date AS invfu_date,
C1.Time AS invfu_time,
C1.assignedById AS invfu_assignedById,
C1.assignedToId AS invfu_assignedtoId,
C1.isactive AS invfu_active,
C2.actionId AS invfu_actionId

from claim_assignedto_history C1 
LEFT JOIN claim_assignedto_historydetail C2
ON C1.ClaimNo = C2.ClmHistoryId
ORDER BY 1 ASC

answered May 05 '18 at 13:15

DxTx

3,049
3
23
34

1

Thank you! That did it and I can see my mistake now from your script.....trying to put the two tables in the 'from' clause where all I really needed it was in the 'left join'. Lesson learned :-) – Jeff May 05 '18 at 16:11
@Jeff, If you like to read, [this](https://stackoverflow.com/q/38549/6327676) and [this](https://www.w3schools.com/sql/sql_join.asp) are great articles to learn about joins. – DxTx May 05 '18 at 16:16
1

@Jeff if this worked, then please accept this as the answer. – Pittsburgh DBA May 06 '18 at 13:26

T-SQL to merge columns from two tables into one and join

1 Answers1