How to insert multiple records in sql?

Rajneesh Chaubey

8y1.2kVisual Basic .NET

I have 3 tables Client, Product and Transaction.

Client table has ClientID is (identity)

ClientID Clientname Email Mobile

Product has ProductID(Identity)

ProdID ClientID Prodname

Transact table has TransactID(Identity)

TransactID ProdID ClientID ParamID

ParamID is the value of those chechboxes.

I want to insert multiple product against single client. Let say there is another button to insert Product(Before Final Insert). On that button we store multiple data on button click in DataTable. It will hold data till we finnaly submit the form. For every Product there can multiple selection of checkbox.

Now final insert will be like that Client details will go to the client tables, and for product say

he has inserted two product:p1 and p2 it will goes to product and take particular client id. Like this:-

ProdID ClientID Prodnam
1 1 p1
2 1 p2

So 2 rows will be affected.

And In transaction table (the most confusing one) multiple rows will be affected such that:

TransactID ProdID ClientID ParamID
1 1 1 2
2 1 1 3
3 2 1 1
4 2 1 4

ParamID indicates that for value of particular checkbox against that product.

So there is multiple rows to be affected in sql server. How do I handle the same in C# codebehind?

I am confused. Please suggest me. Any help would be greatly appreciated. Thank you.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal8y ago
Accepted answer
DECLARE @inClientname NVARCHAR(100)
DECLARE @inEmail NVARCHAR(100)
DECLARE @inMobile NVARCHAR(10)
DECLARE @inProdname NVARCHAR(100)
DECLARE @inInput NVARCHAR(500)
SELECT @inClientname = 'Demo Client',
@inEmail = '[email protected]',
@inMobile = '1234567890',
@inProdname = 'Demo Product',
@inInput = '2,3,1,4'
DECLARE @lClientID INT
DECLARE @lProductID INT
DECLARE @lCharacter CHAR(1) = ','
DECLARE @tempClient AS TABLE
(
ClientID INT IDENTITY(1, 1),
Clientname NVARCHAR(100),
Email NVARCHAR(100),
Mobile NVARCHAR(10)
)
DECLARE @tempProduct AS TABLE
(
ProductID INT IDENTITY(1, 1),
ClientID NVARCHAR(100),
Prodname NVARCHAR(100)
)
DECLARE @tempTransaction AS TABLE
(
TransactID INT IDENTITY(1, 1),
ProdID INT,
ClientID INT,
ParamID INT
)
DECLARE @Output TABLE (value NVARCHAR(1000))
DECLARE @StartIndex INT,
@EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@inInput, LEN(@inInput) - 1, LEN(@inInput)) <> @lCharacter
BEGIN
SET @inInput = @inInput + @lCharacter
END
WHILE CHARINDEX(@lCharacter, @inInput) > 0
BEGIN
SET @EndIndex = CHARINDEX(@lCharacter, @inInput)
INSERT INTO @Output
(
value
)
SELECT SUBSTRING(@inInput, @StartIndex, @EndIndex - 1)
SET @inInput = SUBSTRING(@inInput, @EndIndex + 1, LEN(@inInput))
END
IF NOT EXISTS
(
SELECT 1
FROM @tempClient
WHERE Clientname = @inClientname
AND Email = @inEmail
AND Mobile = @inMobile
)
BEGIN
INSERT INTO @tempClient
(
Clientname,
Email,
Mobile
)
VALUES
(@inClientname, @inEmail, @inMobile)
SELECT @lClientID = @@IDENTITY
END
ELSE
BEGIN
SELECT @lClientID = ClientID
FROM @tempClient
WHERE Clientname = @inClientname
AND Email = @inEmail
AND Mobile = @inMobile
END
IF NOT EXISTS
(
SELECT 1
FROM @tempProduct
WHERE ClientID = @lClientID
AND Prodname = @inProdname
)
BEGIN
INSERT INTO @tempProduct
(
ClientID,
Prodname
)
VALUES
(@lClientID, @inProdname)
SELECT @lProductID = @@IDENTITY
END
ELSE
BEGIN
SELECT @lProductID = ProductID
FROM @tempProduct
WHERE ClientID = @lClientID
AND Prodname = @inProdname
END
INSERT INTO @tempTransaction
(
ProdID,
ClientID,
ParamID
)
SELECT @lProductID,
@lClientID,
value
FROM @Output o
LEFT OUTER JOIN @tempTransaction t
ON o.value = t.ParamID
AND ClientID = @lClientID
AND ProdID = @lProductID
WHERE t.TransactID IS NULL
SELECT *
FROM @tempClient
SELECT *
FROM @tempProduct
SELECT *
FROM @tempTransaction
+3
Rajneesh Chaubey8y ago
@sudipta sanyal That really helped. Thanks a ton!!
+3
Rajneesh Chaubey8y ago
I tried a lot but still not resolved. Seeking for help.
+2
Nitin Sontakke8y ago
Is this resolved?

If not, please do let us know.

Should you need any more help on this, please feel free to contact.
+2
Rajneesh Chaubey8y ago
@Rupesh Kahane That is really a good idea. Thanks!!
+2
Rupesh Kahane8y ago
You can do one thing.
Create one procedure, pass all parameters to that procedure.

In this procedure first insert Client details (after successfully inserted data, you will get clientId by select query i.e. select top 1 ClientId from tableName order by clientId desc)
Now after that insert Product details ( after successfully inserted data, you will get productId as above)
Now insert Transaction details.

Other way you can do different insert operation by regular insert command statement & do the same procedure like make three different procedures.

Note: Logic is only depends upon you that how you are going to implement.

Enjoy!!!!! Happy coding.
+3
Rajneesh Chaubey8y ago
@Nitin Sontakke Sir, I didn't got your point. The data which I am inserting is the real one. I also want to ask you that how do I get those ID's of client and product when inserting in Transact table?
+2
Nitin Sontakke8y ago
It is not clear if the data given above is real one or just the sample one.

If it is real one, then you have very less data which you can send to a stored procedure in just one go and do the manipulation in stored procedure using may be cursor.

Unless the insert actual happens, in parent table, you donot have keys to insert in the child table. This is easy to perform in stored procedure if all your keys are identity.

You can send comma separated values from C# and split them in sql server in function you will find on-line.

Please fill free to let me know if you still need any detailed help!
+2