SQL Server Insert multiple rows conditionally

ftf50wuq 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(137)

I need to insert multiple (hundreds) rows in a table and I don't want to insert each row by hand. There has to be a way to do this, right?

The table looks like this: (SQL Server)
| id | userFK | appUserFK | dateA | createdBy | dateB | changedBy | Flag |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 3 | 7 | 2023-7-28 | Dan | 2023-7-28 | Dan | NULL |
| 2 | 4 | 7 | 2023-7-28 | Dan | 2023-7-28 | Dan | NULL |
| 2 | 5 | 7 | 2023-7-28 | Dan | 2023-7-28 | Dan | NULL |
| 3 | 9 | 12 | 2023-7-28 | Dan | 2023-7-28 | Dan | NULL |

Now I want to add as many rows as appUserFK = 7 has. For every entry that has appUserFK = 7 , I want to add another entry with the same userFK but appUserFK = 16 and also 19 . So that every row where a userFK has a corresponding appUserFK of 7 , also has a row with the appUserFK of 16 and 19 . My single Insert-Query would look like this:

INSERT INTO [myTable].[dbo].[user] (userFK, appUserFK, dateA, createdBy, changedBy)
VALUES (3, 16, GETDATE(), 'Josh', GETDATE(), 'Josh');

INSERT INTO [myTable].[dbo].[user] (userFK, appUserFK, dateA, createdBy, changedBy)
VALUES (4, 16, GETDATE(), 'Josh', GETDATE(), 'Josh');

INSERT INTO [myTable].[dbo].[user] (userFK, appUserFK, dateA, createdBy, changedBy)
VALUES (5, 16, GETDATE(), 'Josh', GETDATE(), 'Josh');

And so on...

Thanks in advance!

I tried this query but it does not work:

INSERT INTO myTable (userFK, appUserFK, dateA, createdBy, changedBy) 
SELECT userFK, 16, GETDATE(), 'Josh', GETDATE(), 'Josh' FROM myTable WHERE appUserFK = 7 
AND NOT EXISTS (SELECT userFK FROM myTable WHERE appUserFK = 16);

sql-server

来源：https://stackoverflow.com/questions/76786756/insert-multiple-rows-conditionally

3条答案

按热度按时间

pgccezyw1#

You can use a combination of INSERT and SELECT to achieve what you want. I have an example for you:

INSERT INTO Customers_Backup (CustomerID, CustomerName, ContactName,     Country)
SELECT CustomerID, CustomerName, ContactName, Country 
FROM Customers
WHERE Country='Mexico'

This query inserts all rows from the customers table into customers_backup which have the country 'Mexico'. Instead of copying all data you can just use hardcoded data as well. Let me know if you need further assistance or if this hint is enough for you to work on your exact problem.

赞(0）回复(0）举报 2023-08-02

huus2vyu2#

You can INSERT based on a SELECT query rather than individually. This would work even if more records were added where appUserFK is 7. The UNION ALL part combines the two SELECTs so they can be INSERTed together.

Something like this:

DECLARE @DateNow AS date

SET @DateNow = GETDATE() -- to save repeatedly fetching the date

INSERT INTO [user] (userFK, appUserFK, dateA, createdBy, dateB, changedBy)
  SELECT 
    userFK, 
    16, 
    @DateNow, 
    'Josh', 
    @DateNow,
    'Josh'
  FROM
    [user]
  WHERE
    appUserFK = 7
  UNION ALL
  SELECT 
    userFK, 
    19, 
    @DateNow, 
    'Josh', 
    @DateNow,
    'Josh'
  FROM
    [user]
  WHERE
    appUserFK = 7

赞(0）回复(0）举报 2023-08-02

kq0g1dla3#

You can use The SQL INSERT INTO SELECT Statement:

insert into [user](userFK, appUserFK, dateA, createdBy, dateB, changedBy)
select userFK, 16, GETDATE(), createdBy, GETDATE(), changedBy
from [user]
where appUserFK = 7
union all
select userFK, 19, GETDATE(), createdBy, GETDATE(), changedBy
from [user]
where appUserFK = 7

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Insert multiple rows conditionally

3条答案

相关问题

热门标签

最新问答