CODEWITHSUNDEEP
sql
user3498953
user3498953

Reputation: 25

Insert Data into Table if a condition is true

SQL statement Question

I have an instance where I need to insert one record multiple times in a DB table that contains a unique reference column. I'm declaring a variable and using a select statement to populate variable and then running an insert statement. The issue is that when grabbing unique value into variable it only grabs the last record in the reference table. I need for insert statement to insert at each point where a record is found.

DECLARE @ID INT;
DECLARE @RuleID INT;

SELECT @RuleID = RuleID from Rules where Rule_Name = 'VERSION_ID' and Field = 'TSH'
SELECT @ID = ID FROM CHANNELS WHERE SUBSTRING(CHANNEL_NAME,0,4) != 'HEL'

BEGIN
INSERT INTO Rule_Items
VALUES(@ID,@RuleID,0,'2.5.1','E','A',0,getdate())
END

Upvotes: 0

Views: 2428

Answers (2)

user3498953
user3498953

Reputation: 25

I decided to use a cursor and create a temp table. Here is query that worked.

**--BEGIN TRAN
DECLARE @Channelid INT
DECLARE @RuleID INT
SELECT  @RuleID = RuleID From Rules Where Rule_Name = 'VERSION_ID'

DECLARE GetChannelId CURSOR FOR SELECT Channelid FROM HL7_Channels WHERE Channel_Name not like 'MU2%' AND Channel_Description LIKE '%outbound%'
OPEN GetChannelId
FETCH GetChannelId INTO @Channelid
WHILE (@@FETCH_STATUS = 0)
      BEGIN
            IF NOT EXISTS (Select 1 From Rule_Items Where ChannelId = @Channelid AND RuleID = @RuleID)
            BEGIN
                  INSERT INTO Rule_Items VALUES (@Channelid,@RuleID,0,'2.5.1','E','A',0,getdate())
            END         
FETCH GetChannelId INTO @Channelid
CONTINUE 
      END
CLOSE GetChannelId
DEALLOCATE GetChannelId

--ROLLBACK TRAN
--COMMIT TRAN**

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want all combinations, you can use insert . . . select with a cross join:

INSERT INTO Rule_Items
    select c.id, r.ruleid, 0, '2.5.1', 'E', 'A', 0, getdate()
    from rules r cross join
         channels c
    where r.Rule_Name = 'VERSION_ID' and r.Field = 'TSH' and
          SUBSTRING(c.CHANNEL_NAME, 0, 4) <> 'HEL';

Upvotes: 1

Related Questions