In my previous blog, I explained how we can achieve bulk insertion with Table value types in Entity Framework. After that, while working with Node.js API, I encountered with the same scenario there also, where I neede to insert bulk data from the application.
At that time, this clicked in my mind that many of us may have faced the same issue and if it is possible to use Table value type in Node.js.
The answer is "YES". We can insert bulk data using Table value types in Node.js too. So, we are going to implement Table value type in Node.js. To implement it, we just need to follow some steps as given below.
Prerequisites
- MS SQL Server
- Node.js installed with packages (Express, MSSql, and body-parser)
Step 1
Create Table value type.
- CREATE TYPE [dbo].[Tvp_Employee] AS TABLE(
- [Name] [varchar](50) NULL,
- [Salary] [numeric](18, 0) NULL
- )
- GO
Step 2
After creating Table value type, create a stored procedure that will use the created Table value type as parameter.
- Create PROCEDURE [dbo].[Proc_insertemployee] (@tbl_Employee TVP_EMPLOYEE readonly)
- AS
- BEGIN
- BEGIN try
-
- INSERT INTO tbl_employee
- (NAME,
- salary)
- SELECT NAME,
- salary
- FROM @tbl_Employee
- Select 1 as 'Code', 'Inserted Successfuly.' as 'Message'
- END try
- BEGIN catch
- DECLARE @ErrorNumber INT
- DECLARE @ErrorMessage VARCHAR(2000)
- DECLARE @ErrorSeverity INT
- DECLARE @ErrorState INT
- SELECT @ErrorNumber = Error_number(),
- @ErrorMessage = 'Error occured at time of inserting'
- + Error_message(),
- @Errorseverity = Error_severity(),
- @ErrorState = Error_state()
- RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)
- END catch
- END
Step 3
Make an SQL table using Node.js and respective columns. It should match the fields of the table value type that we created in SQL.
- var tvp_Emp = new sql.Table();
- // Columns must correspond with type we have created in database.
- tvp_Emp.columns.add('Name', sql.VarChar(50));
- tvp_Emp.columns.add('Salary', sql.Decimal(5, 0));
Step 4
Add records in that table.
- // Add data into the table that will be pass into the procedure
- for (var i = 1; i <= 5; i++) {
- tvp_Emp.rows.add('MiteshGadhiya_' + i, (10000 * i));
- }
Step 5
Now, set that table as input parameter.
- request.input('tbl_Employee', tvp_Emp);
Step 6
Execute the stored procedure with that input parameter.
- //Execute Store procedure
- request.execute('Proc_insertemployee', function (err, recordsets, returnValue) {
- console.dir(JSON.stringify(recordsets[0][0]));
- res.end(JSON.stringify(recordsets[0][0]));
- });
Step 7
Run your API and test it. It will insert all the records in database.
#CMD
#PostMan API check
That’s it; we are done.
Hope this information will be helpful.