How to do the bulk insert of one lack records in sql server using node js

Question

Hi, I will store the data in following Json format. Now I want to insert this one lack. records in sql server. Kindly Please provide the best solution and if it's possible share your solution with sql query.

[
{
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value
},
{
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value,
key: value
},
....(100000)
]

Do the needful. Advance Thanks.

Answer 1

your question is not clear. to read the json file as records use a library like :

https://github.com/dominictarr/JSONStream

the sql server client for node is:

https://www.npmjs.com/package/mssql

you probably want to use the .bulk() function.

Answer 2

Hello @Arutprakasam !

THIS IS NOT APPORPRIATE FOR SUCH A LARGE NUMBER

I leave it just for reference i can provide a sample from my Workshop https://www.cloudblogger.eu/2024/01/14/azure-vm-auto-provisioning-web-app-with-logic-apps-approval-workflow/

const express = require('express');
const fetch = require('node-fetch');
const { DefaultAzureCredential } = require('@azure/identity');
const { SecretClient } = require('@azure/keyvault-secrets');
const sql = require('mssql');

const osMapping = {
    "Windows 11": {
        imageOffer: "windows-11",
        imageSku: "win11-22h2-pro"
    },
    "Windows 10": {
        imageOffer: "windows-10",
        imageSku: "win10-22h2-pro-g2"
    },
    // Add other OS mappings here
};

function processVmConfig(requestBody) {
    const osDetails = osMapping[requestBody.os];
    if (!osDetails) {
        throw new Error('Unsupported OS selected');
    }
    return {
        vmName: requestBody.name,
        imageOffer: osDetails.imageOffer,
        imageSku: osDetails.imageSku,
        vmSize: requestBody.size,
        region: requestBody.region,
        username: requestBody.username // Include the username in the processed config
    };
}

const app = express();
const port = 3001;
app.use(express.json());

// Azure Key Vault details
const credential = new DefaultAzureCredential();
const vaultName = process.env["KEY_VAULT_NAME"];
const url = `https://${vaultName}.vault.azure.net`;
const client = new SecretClient(url, credential);

// Function to get secret from Azure Key Vault
async function getSecret(secretName) {
    const secret = await client.getSecret(secretName);
    return secret.value;
}

// Function to connect to Azure SQL Database
async function getSqlConfig() {
    const username = await getSecret("sql-admin");
    const password = await getSecret("sql-pass");
    const server = await getSecret("sql-server");
    const database = await getSecret("sql-db");

    return {
        user: username,
        password: password,
        server: server,
        database: database,
        options: {
            encrypt: true,
            trustServerCertificate: false
        }
    };
}

app.post('/provision-vm', async (req, res) => {
    try {
        const vmConfig = processVmConfig(req.body);

        // Connect to the SQL database
        let pool = await sql.connect(await getSqlConfig());
        
        // Perform the SQL INSERT operation
        let result = await pool.request()
            .input('username', sql.NVarChar, vmConfig.username) // Add username input
            .input('vmName', sql.NVarChar, vmConfig.vmName)
            .input('imageOffer', sql.NVarChar, vmConfig.imageOffer)
            .input('imageSku', sql.NVarChar, vmConfig.imageSku)
            .input('vmSize', sql.NVarChar, vmConfig.vmSize)
            .input('region', sql.NVarChar, vmConfig.region)
            .query(`
                INSERT INTO vmprovs 
                (Username, VmName, ImageOffer, ImageSku, VmSize, Location) 
                VALUES 
                (@username, @vmName, @imageOffer, @imageSku, @vmSize, @region)
            `);

        res.status(201).send({ message: 'VM provisioning data saved successfully' });
    } catch (error) {
        console.error("Error details:", error);
        res.status(500).send({ message: 'Error saving VM provisioning data', error: error.message });
    }
});

app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`);
});

I hope this helps! Kindly mark the answer as Accepted and Upvote in case it helped! Regards