{"id":3904,"date":"2024-07-14T22:18:39","date_gmt":"2024-07-14T15:18:39","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3904"},"modified":"2024-07-22T07:47:57","modified_gmt":"2024-07-22T00:47:57","slug":"nodejs-sql-server-insert","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-insert\/","title":{"rendered":"Node.JS SQL Server: Insert"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert data into a table in SQL Server from a Node.js<\/code> program.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0Connecting to SQL Server from Node.js tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Inserting a new row into the table #<\/a><\/h2>\n\n\n\n

Step 1. Create a new file called insert.js<\/code> in the project directory.<\/p>\n\n\n\n

Step 2. Add the following code to the insert.js<\/code> to insert a new author into the Authors<\/code> table:<\/p>\n\n\n

import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nconst<\/span> insertAuthor = async<\/span> (firstName, lastName, birthDate) => {\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    await<\/span> sql.connect(config);\n\n    \/\/ Execute the insert statement<\/span>\n    await<\/span> sql.query`INSERT INTO Authors (FirstName, LastName, BirthDate) \n                    VALUES (${firstName}<\/span>, ${lastName}<\/span>, ${birthDate}<\/span>)`<\/span>;\n\n    console<\/span>.log(`The author ${firstName}<\/span> ${lastName}<\/span> was inserted!`<\/span>);\n  } catch<\/span> (err) {\n    console<\/span>.error('Error inserting new author:'<\/span>, err);\n  } finally<\/span> {\n    \/\/ Close the database connection<\/span>\n    await<\/span> sql.close();\n  }\n};\n\ninsertAuthor('Agatha'<\/span>, 'Christie'<\/span>, '1890-09-15'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the sql<\/code> object from the mssql<\/code> module and config<\/code> object from the config<\/code> module:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define a function insertAuthor<\/code> that has three parameters firstName<\/code>, lastName<\/code>, and birthDate<\/code>:<\/p>\n\n\n
const<\/span> insertAuthor = async<\/span> (firstName, lastName, birthDate) => {<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to the SQL Server by calling the connect method of the sql<\/code> object with the config<\/code> object:<\/p>\n\n\n
await<\/span> sql.connect(config);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute the INSERT<\/code> statement:<\/p>\n\n\n
 await<\/span> sql.query`INSERT INTO Authors (FirstName, LastName, BirthDate) \n                 VALUES (${firstName}<\/span>, ${lastName}<\/span>, ${birthDate}<\/span>)`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, show a message on the console to indicate that the author has been inserted successfully:<\/p>\n\n\n
console<\/span>.log(`The author ${firstName}<\/span> ${lastName}<\/span> was inserted!`<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, if an error occurs during the insertion process, display the error message in the catch<\/code> block:<\/p>\n\n\n
console<\/span>.error('Error inserting new author:'<\/span>, err);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, close the database connection in the finally<\/code> block by calling the close()<\/code> method of the sql<\/code> object:<\/p>\n\n\n
await<\/span> sql.close();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, call the insertAuthor<\/code> function to insert the author with the first name Agatha<\/code>, last name Christie<\/code>, and birth date 1890-09-15<\/code> into the Authors<\/code> table:<\/p>\n\n\n
insertAuthor('Agatha'<\/span>, 'Christie'<\/span>, '1890-09-15'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Execute the following npm<\/code> command to run the insert.js<\/code> file:<\/p>\n\n\n
npm --env-file=.env insert.js<\/code><\/span><\/pre>\n\n\n
Verifying the insert #<\/a><\/h3>\n\n\n\n
Step 1. Open SQL Server Management Studio and connect the BookStore<\/code> database.<\/p>\n\n\n\n
Step 2. Query data from the Authors<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> Authors<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
AuthorID | FirstName | LastName | BirthDate\n---------+-----------+----------+------------\n1        | Agatha    | Christie | 1890-09-15\n(1 row)<\/code><\/span><\/pre>\n\n\n
Insert multiple rows into a table #<\/a><\/h2>\n\n\n\n
Step 1. Create a new file named insert-many.js<\/code> in the project directory.<\/p>\n\n\n\n
Step 2. Add the following code to the insert-many.js<\/code> to insert multiple authors from an array into the Authors<\/code> table:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nasync<\/span> function<\/span> insertAuthors<\/span>(authors<\/span>) <\/span>{\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    await<\/span> sql.connect(config);\n\n    \/\/ Construct the insert query<\/span>\n    let<\/span> query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES '<\/span>;\n\n    \/\/ Build the values part of the query<\/span>\n    const<\/span> values = authors\n      .map(\n        ({ firstName, lastName, birthDate }<\/span>) =><\/span>\n          `('${firstName}<\/span>', '${lastName}<\/span>', '${birthDate}<\/span>')`<\/span>\n      )\n      .join(', '<\/span>);\n\n    \/\/ Complete the query<\/span>\n    query += values;\n\n    \/\/ Execute the query<\/span>\n    await<\/span> sql.query(query);\n\n    console<\/span>.log(`${authors.length}<\/span> authors were inserted successfully!`<\/span>);\n  } catch<\/span> (err) {\n    console<\/span>.error('Error inserting authors:'<\/span>, err);\n  } finally<\/span> {\n    \/\/ Close the database connection<\/span>\n    await<\/span> sql.close();\n  }\n}\n\nconst<\/span> authors = [\n  { firstName<\/span>: 'George'<\/span>, lastName<\/span>: 'Orwell'<\/span>, birthDate<\/span>: '1903-06-25'<\/span> },\n  { firstName<\/span>: 'Jane'<\/span>, lastName<\/span>: 'Austen'<\/span>, birthDate<\/span>: '1775-12-16'<\/span> },\n  { firstName<\/span>: 'Leo'<\/span>, lastName<\/span>: 'Tolstoy'<\/span>, birthDate<\/span>: '1828-09-09'<\/span> },\n];\n\n\/\/ Call the function to insert the authors<\/span>\ninsertAuthors(authors);\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works (focusing on the new part)<\/p>\n\n\n\n
\/\/ Construct the insert query<\/span>\nlet<\/span> query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES '<\/span>;\n\n\/\/ Build the values part of the query<\/span>\nconst<\/span> values = authors\n  .map(\n    ({ firstName, lastName, birthDate }<\/span>) =><\/span>\n      `('${firstName}<\/span>', '${lastName}<\/span>', '${birthDate}<\/span>')`<\/span>\n  )\n  .join(', '<\/span>);\n\n\/\/ Complete the query<\/span>\nquery += values;\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, execute the insert statement using the query()<\/code> method of the sql<\/code> object:<\/p>\n\n\n
await<\/span> sql.query(query);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, initialize an array of author objects for insertion:<\/p>\n\n\n
const<\/span> authors = [\n  { firstName<\/span>: 'George'<\/span>, lastName<\/span>: 'Orwell'<\/span>, birthDate<\/span>: '1903-06-25'<\/span> },\n  { firstName<\/span>: 'Jane'<\/span>, lastName<\/span>: 'Austen'<\/span>, birthDate<\/span>: '1775-12-16'<\/span> },\n  { firstName<\/span>: 'Leo'<\/span>, lastName<\/span>: 'Tolstoy'<\/span>, birthDate<\/span>: '1828-09-09'<\/span> },\n];<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In practice, you may read the data from a CSV file or get the data from an API call.<\/p>\n\n\n\n
Finally, call the insertAuthors()<\/code> function to insert the author objects from the authors<\/code> array:<\/p>\n\n\n
insertAuthors(authors);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the following command in your terminal to execute the index-many.js<\/code> file:<\/p>\n\n\n
node --env-file=.env insert-many.js<\/code><\/span><\/pre>\n\n\n
If you see the following message, you have successfully inserted three authors from the array into the Authors<\/code> table.<\/p>\n\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Construct an INSERT<\/code> statement using the tagged template literals to insert a new row into a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
First, construct an insert statement that inserts multiple rows<\/a> into the Authors<\/code> table:<\/p>\n\n\n
Notice that the mssql<\/code> package leverages the tagged template literals<\/a> to construct SQL statements in a way that automatically handles SQL injection prevention and parameterized query.<\/p>\n\n\n\n
Please note that the config object contains the database configuration defined in the connecting to the SQL Server from the Node.js program tutorial<\/a>.<\/p>\n\n\n\n