How to insert value in flag without using update statement-CodePudding

I am using the below query to insert data in the table, and at the end using an update statement to update the flag. Can I insert value without using update statement?

The update statement depends on the value of count_polls and total_count if they are equal then flag should be 1 otherwise 0

Declare @batchid varchar(4000)
set @batchid = (select batch id from polls_batch)
INSERT INTO [dbo]. [FeedSummary_test] (count_polls, total_count, BatchId, FeedLogStatus)

SELECT COUNT(*), ?, 0, 0 FROM [dbo].[PollsFeed_1] where batchid = @batchid

union

SELECT COUNT(*), ?, 0, 0 FROM [dbo].[PollsFeed_2] where batchid = @Batchid

Union

SELECT COUNT(*), ?, 0, 0 FROM [dbo].[PollsFeed_3] where batchid = @batchid

update [dbo].[FeedSummary_test]
set Flag = IIF(count_polls=total_count,1,0)

I am getting these question marks from variables in SSIS, result should be something like this:

The query for these question marks is this:

select(select count(*) from dbo.AR_Feed_one) count_1,
(select count(*) from dbo.AR_Feed_two) count_2,
(select count(*) from dbo.AR_Feed_three )count_3

And then I am storing these values in the variables and inserting them in the query using ?.

Final table should look like this
Count_polls,    TotalCount,     BatchID,    FeedLogStatus.  Flag
100 100 BUF 0   1
200 200 BUF 0   1
150 120 BUF 0   0

And for a particular batchId there will be three rows with different total count and count polls.

CodePudding user response：

You could add your initial select into a common table expression, which allows you to use the result in your insert:

Declare @batchid varchar(4000)
set @batchid = (select batch id from polls_batch)

;WITH cte AS (
  SELECT COUNT(*) AS count_polls, ? AS total_count, 0 AS BatchId, 0 AS FeedLogStatus FROM [dbo].[PollsFeed_1] where batchid = @batchid
  
  UNION
  
  SELECT COUNT(*), ?, 0, 0 FROM [dbo].[PollsFeed_2] where batchid = @Batchid
  
  UNION
  
  SELECT COUNT(*), ?, 0, 0 FROM [dbo].[PollsFeed_3] where batchid = @batchid
)
INSERT INTO [dbo]. [FeedSummary_test] (count_polls, total_count, BatchId, FeedLogStatus, Flag )
SELECT count_polls, total_count, BatchId, FeedLogStatus, IIF(count_polls=total_count,1,0)
FROM cte