SQL Server If ID exists in Table 1 but not in Table 2, insert multiple rows into Table 2 including the ID from Table 1

hs1ihplo 于 2023-04-19 发布在其他

关注(0)|答案(1)|浏览(155)

Alright, so I have a table let's call Profiles and another table called Rates. Due to some application changes over time, some accounts are missing their Rates data. I need to write an INSERT that takes all the IDs from Profiles and checks to see if they match to any rows (ProfileID column) in Rates. If there are no matches for Profiles.ID to Rates.ProfileID, then I need to insert 4 rows with default values and the ProfileID.

Searching the stack, most people only need to insert one row, so I'm finding it challenging to connect CROSS JOIN with multiple VALUES entries, just not sure which is the right way to go with it. I also don't have vast experience with SQL.

I'm somewhere around here:

INSERT into Rates (Discount, MinVal, MaxVal, ProfileID)
SELECT ID FROM [DBName].[Prefix].[Profiles]
CROSS JOIN (VALUES ((0.000, 0.00000, 249.00000, ID), 
(0.000, 250.00000, 499.00000, ID),
(0.000, 500.00000, 999.00000, ID),
(0.000, 999.00000, 99999.99999, ID)))
-- ^ this doesn't work, it gives column name invalid
LEFT JOIN Rates ON Profiles.ID = Rates.ProfileID
WHERE Rates.ProfileID IS NULL

Am I close or way off?

sql-server

来源：https://stackoverflow.com/questions/75979841/if-id-exists-in-table-1-but-not-in-table-2-insert-multiple-rows-into-table-2-in

1条答案

按热度按时间

guykilcj1#

Thanks to Thom A for the nudge. Leaving for Posterity

INSERT into Rates (Discount, MinVal, MaxVal, ProfileID)
SELECT T2.Discount, T2.MinVal, T2.MaxVal, T1.ID FROM Profiles as T1
CROSS APPLY (VALUES (0.000, 0.00000, 249.00000),
(0.000, 250.00000, 499.00000),
(0.000, 500.00000, 999.00000),
(0.000, 999.00000, 99999.99999)) AS T2(Discount, MinVal, MaxVal)
LEFT JOIN Rates ON T1.ID = Rates.ProfileID 
WHERE Rates.ProfileID IS NULL

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server If ID exists in Table 1 but not in Table 2, insert multiple rows into Table 2 including the ID from Table 1

1条答案

相关问题

热门标签

最新问答