SQL Server Performance Forum – Threads Archive

How can I insert more than one row in a table?

How can I insert more than one row in a table with a SP that receives these parameters: @Certificate_Management0 smallint = null, @Certificate_Management1 smallint = null,
@Certificate_Management2 smallint = null, @Certificate_Management3 smallint = null,
@Certificate_Management4 smallint = null, @Certificate_Management5 smallint = null,
@Certificate_Management6 smallint = null, @Other_certificates varchar(150)
Each one of these parameters, if not are null, represents a new row in this table: CertMana_Offers Table Cert_manaOffers_id = 267 bigint
Offer_num = 258 bigint (FK Offer_id)
Certificate_Mana = 2 smallint (FK Certificate_id)
Other_certificates = E-Base, Q-Base varchar (150) Where every @Certificate_Management0..6 parameter different to null is stored in Certificate_Mana column, @Other_certificates different to null is stored in Other_certificates column, and I also insert the offer number retrieved with SCOPE_IDENTITY() in the ‘Offers#%92 table insert process. When the user inserts an offer, in the Offers table, he/she can specify up to seven certificates. So, for example the offer number 258 can have 7 certificates in the CertMana_Offers Table, every one in a different row.
My question is: How can I insert those @Certificate_Management0..7 parameters in different rows of the CertMana_Offers table? Thank you,
Cesar

---

Are you looking for some testing like this?
DECLARE @c1 INT
DECLARE @c2 INT
SELECT @c1 = NULL, @c2=1 IF @c1 IS NULL
PRINT ‘First not initialized, do not INSERT’
ELSE
PRINT ‘Initialized, INSERT’
PRINT ‘—‘
IF @c2 IS NULL
PRINT ‘Secondd not initialized, do not INSERT’
ELSE
PRINT ‘Initialized, INSERT’ First not initialized, do not INSERT
—
Initialized, INSERT
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Hi, No, I am looking for the way to insert these parameters received from the application to ‘CertMana_Offers’ table. Thanks

---

Basically the same, just replace the PRINT in my example by your INSERT statement. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

I meant something like this: INSERT INTO CertMana_Offers (Offer_num, Certificate_Mana)
SELECT @OfferID, Certificate_Mana
FROM
( SELECT @Certificate_Management0 as Certificate_Mana UNION
SELECT @Certificate_Management1 UNION
SELECT @Certificate_Management2 UNION
SELECT @Certificate_Management3 UNION
SELECT @Certificate_Management4 UNION
SELECT @Certificate_Management5 UNION
SELECT @Certificate_Management6
) AllCerts
WHERE Certificate_Mana IS NOT NULL
CertMana_Offers table: Cert_manaOffers_id = 267 bigint
Offer_num = 258 (FK Offer_id) bigint
Certificate_Mana = 2 (FK Certificate_id) smallint
Do you know how can I modify this SP to insert data in the CertMana_Offers table so that the data received (for example @Certificate_Management0 = 4, @Certificate_Management1 = 2 and @Certificate_Management2 = 6) is inserted in the same order of parameters, thus?: Cert_manaOffers_id = 267 / 268 / 269
Offer_num = 258 / 258 / 258
Certificate_Mana = 4 / 2 / 6 Instead of thus: (Now it is inserted thus)
Cert_manaOffers_id = 267 / 268 / 269
Offer_num = 258 / 258 / 258
Certificate_Mana = 2 / 4 / 6 I mean, inserting the parameters in the same order are received, in the example 4, 2 and 6 instead of 2, 4 and 6.
I created this table (CertMana_Offers) in order to have all the certificates of every offer, in the Offers table, in a separate table. Is it correct that in this new table I create the Cert_manaOffers_id column (which I don#%92 t need) only to have the primary key and the identity? Or exists another better alternative? Thanks

---

I would suggest you change the Offer_num column to have an IDENTITY constraint … IF @Certificate_Management0 IS NOT NULL
BEGIN
INSERT INTO CertMana_Offers (Certificate_Mana) VALUES (@Certificate_Management0)
END
IF @Certificate_Management1 IS NOT NULL
BEGIN
INSERT INTO CertMana_Offers (Certificate_Mana) VALUES (@Certificate_Management1)
END etc. Without the IDENTITY column, you would need something like: IF @Certificate_Management0 IS NOT NULL
BEGIN
INSERT INTO CertMana_Offers (Offer_num, Certificate_Mana)
VALUES ((SELECT ISNULL(MAX(OfferNum), 0) + 1 FROM CertMana_Offers), @Certificate_Management1)
END
etc.

---

I don#%92 t understand what you mean. Now my CertMana_Offers table is thus:
CREATE TABLE [CertMana_Offers] (
[Cert_manaOffers_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Offer_num] [bigint] NULL ,
[Certificate_Mana] [smallint] NULL ,
CONSTRAINT [PK_Cert_manaOffers] PRIMARY KEY CLUSTERED
(
[Cert_manaOffers_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_CertMana_Offers _Certificates_name] FOREIGN KEY
(
[Certificate_Mana]
) REFERENCES [Certificates_name] (
[Certificates_name_id]
),
CONSTRAINT [FK_CertMana_Offers_Offers] FOREIGN KEY
(
[Offer_num]
) REFERENCES [Offers] (
[Offer_id]
)
) ON [PRIMARY]
GO Your code works fine. But also works with this hack:
INSERT INTO CertMana_Offers (Offer_num, Certificate_Mana)
SELECT @OfferID, Certificate_Mana
FROM
( SELECT 0 As OrdinalPos, @Certificate_Management0 as Certificate_Mana UNION
SELECT 1, @Certificate_Management1 UNION
SELECT 2, @Certificate_Management2 UNION
SELECT 3, @Certificate_Management3 UNION
SELECT 4, @Certificate_Management4 UNION
SELECT 5, @Certificate_Management5 UNION
SELECT 6, @Certificate_Management6
) AllCerts
WHERE Certificate_Mana IS NOT NULL
Which system is better? Thanks

---

One of your points was that the parameters could be null, and if so they should not be inserted. In your script, you are still inserting these values.

---

change the UNION to UNION ALL to avoid a sort, which affects the insert order

---

It is true! Adding that magic word ‘All#%92 the order of the received parameters is maintained in the table. Thank you. Do you know how can I retrieve these values in the same way once inserted? I mean using the same parameters name and in the same given order. I need this operation in a SP that returns these parameters to refill the application web form in case the user go back to that form through a special button.
I suppose that it#%92 s very similar to the way we insert them.
Adriaan
But I use ‘WHERE Certificate_Mana IS NOT NULL#%92 at the end of the SP, this doesn#%92 t avoid what you said? In my table is only inserted values different to null.. I don#%92 t understand very well what you mean. Which are the consequences in performance terms and design about what you say?

---

The consequence is that the OrdinalPos column can have gaps – certainly not a big issue, but perhaps you have queries depending on an OrdinalPos range with no gaps (within each group).

---

]]>