Insert objects into database

Question

I have ten thousand records in the postObj object and need to insert into a SQL Server database.

The object structure :

{
  postObj : [
     {
      start_coordinates":  {
                    "latitude": 1.373211,
                    "longitude": 103.955124
    },
    "end_coordinates": {
                 "latitude": 1.31974,
                    "longitude": 103.889633
    },
      longitude: 114.063042,
      latitude: 22.366266,
      event_name : '社交聚會'
      event_country: 'New Yotk',
      event_date: '2021-06-15 00:02:18-15',

    },
 {......
  }
]

SQL Server database table structure:

event_name , nvarchar(500)
event_country, nvarchar(500)
event_date, datetimeoffset(7)
start_coordinates_latitude, varchar(100)
start_coordinates_longitude, varchar(100)
end_coordinates_latitude, varchar(100)
end_coordinates_longitude, varchar(100)

insert js code

        sql.connect(dbConfig, err => {
        const request =  new sql.Request();
    
        let cols = [];
        let inputs = [];
        for (let k in postObj) {
            request.input(k, postObj[k]);
            cols.push(k);
            inputs.push('@' + k);
        }
     
        let query = `insert into test (${cols.toString()}) values (${inputs.toString()})`;
    
        request.query(query, (err, result) => {

              console.log(err);
            //stuff here
        });

I have following issue:

1. filed [event_date] cause the error:

Conversion failed when converting date and/or time from character string.

2. filed [event_name ] , do I need to put the "N" (like SET @something = N'sometext') for Chinese characters, if need, how to do it?

3. how to split the sub-object of start_coordinates into start_coordinates_latitude and start_coordinates_longitude?

Thanks for the help

Answer 1

Here are the possible causes for each problem:

1. Event date is causing the error because the MSSQL doesn't support the time format you are trying to insert. Check this post for available time formats is MSSQL

Conversion failed when converting date and/or time from character string while inserting datetime

2. The solution for the event name as you said is to use the N in order to work for different languages. Check some examples here

How do I insert Chinese characters into a SQLExpress text field?

3. The easiest way you can split the start_coordinates is by looping through each object in you array and creating the start_coordinates_latitude and the start_coordinates_longtitude as follows

This will create the 2 new properties

object.start_coordinates_latitude = start_coordinates.latitude;
object.start_coordinates_longitude = start_cordinates.longitude;

Delete the old property start_coordinates

delete object.start_coordinates;