How to insert multiple records in sql?

Question

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.

sudipta sanyal · 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 = 'democlient@g.com', @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

Rajneesh Chaubey · Answer

@sudipta sanyal That really helped. Thanks a ton!!

Rupesh Kahane · Answer

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.

Rajneesh Chaubey · Answer

I tried a lot but still not resolved. Seeking for help.

Nitin Sontakke · Answer

Is this resolved? If not, please do let us know. Should you need any more help on this, please feel free to contact.

Rajneesh Chaubey · Answer

@Rupesh Kahane That is really a good idea. Thanks!!

Rajneesh Chaubey · Answer

@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?

Nitin Sontakke · Answer

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!