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:

filed [event_date] cause the error:

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

filed [event_name ] , do I need to put the "N" (like SET @something = N'sometext') for Chinese characters, if need, how to do it?
how to split the sub-object of start_coordinates into start_coordinates_latitude and start_coordinates_longitude?

Thanks for the help

'2021-06-15 00:02:18-15' - this is not a date, what's with the "-15" at the end? i would probably use an openjson call and put the whole thing into that, should be quicker than doing one by one — siggemannen
– siggemannen, Commented Jun 16, 2023 at 8:38

PTsag · Accepted Answer · 2023-06-16 09:57:59Z

0

Here are the possible causes for each problem:

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

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?

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;

answered Jun 16, 2023 at 9:57

PTsag

1461 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jenny Hui Over a year ago

for Item 1, I update the object value directly , object.event_date = obj.event_date + ":00" Thanks .

Collectives™ on Stack Overflow

Insert objects into database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related