CODEWITHSUNDEEP
sqlsql-server
tigeravatar
tigeravatar

Reputation: 26640

Insert multiple values into a table without a loop

Objective: I am trying to write a SQL script that will lookup CardFormatIDs from one table (.dbo.CardFormat) and then insert those CardFormatIDs along with additional data into another table (.dbo.CardFormat_CardFormatMetaData)

What I've tried so far: I have written the following SQL script that successfully completes my stated objective, but to do so it is using a CURSOR loop.

Question: Is there an alternate/better way to do this without using the CURSOR loop?

USE [CardWizard]
GO

DECLARE @tblCursor CURSOR;
DECLARE @CFID INT;

BEGIN

  SET @tblCursor = CURSOR FOR
    SELECT CardFormatID FROM .dbo.CardFormat WHERE isDeleted = 'False'

  OPEN @tblCursor
  FETCH NEXT FROM @tblCursor
    INTO @CFID

  WHILE @@FETCH_STATUS = 0
  BEGIN

    INSERT INTO .dbo.CardFormat_CardFormatMetaData(CardFormatID, CardFormatMetaDataID, MetaDataValue, MinimumLength, MaximumLength)
      VALUES(@CFID, 114, 'True', 0, 5)

    FETCH NEXT FROM @tblCursor
      INTO @CFID

  END;

END;

Using MS SQL Server 2017 Express

Database Name: CardWizard

Table 1 (getting info from): CardWizard.dbo.CardFormat:
CardFormatID    CardFormatName    BIN       isDeleted
1               TestFormat1       123456    False
2               TestFormat2       234567    True
3               TestFormat3       345678    False

Table 2 (inputting info to): CardWizard.dbo.CardFormat_CardFormatMetaData:
CardFormatID    CardFormatMetaDataID    MetaDataValue    MinimumLength   MaximumLength
(no values yet) (no values yet)         (no values yet)  (no values yet) (no values yet)

Expected Results: 'TestFormat1' and 'TestFormat3' (CardFormatIDs 1 and 3 because their IsDeleted values are False) get added to the CardFormat_CardFormatMetaData table along with additional values for CardFormatMetaDataID, MetaDataValue, MinimumLength, and MaximumLength (these additional values are static: 114, 'True', 0, 5 respectively)

CardWizard.dbo.CardFormat_CardFormatMetaData:
    CardFormatID    CardFormatMetaDataID    MetaDataValue    MinimumLength   MaximumLength
    1               114                     'True'           0                5
    3               114                     'True'           0                5

Upvotes: 0

Views: 1994

Answers (1)

Ryan Wilson
Ryan Wilson

Reputation: 10765

--You can just do a bulk INSERT with your SELECT statement, and use the static
--values for the other 4 columns
INSERT INTO .dbo.CardFormat_CardFormatMetaData
(CardFormatID, CardFormatMetaDataID, MetaDataValue, MinimumLength, MaximumLength)
SELECT CardFormatID, 114, 'True', 0, 5 
FROM .dbo.CardFormat 
WHERE isDeleted = 'False'

Upvotes: 6

Related Questions