2

I'm trying to to make a script that reads from a nosql and inserts into a SQL Server database.

That said I'm reading collections dynamically, so I need something to do things like 

var columns = [ 1, 2, 3, 4 ...]
var values = [a, b, c ,4 ...]
request.query("INSERT INTO TABLE (" + [columns] + ") VALUES ( " [values] ");"

I have some collections with up to like 27 columns and I can't hog the database by inserting each value as I have like 20.000.000 registers to do... can't find anything that can do that inside a transaction, so I would appreciate any suggestions

Zoe
  • 27,060
  • 21
  • 118
  • 148
Carlos Eduardo Teixeira
  • 21
  • 1
  • 1
  • 6
  • Which version of sql-server? You can type SELECT @@VERSION to find out if you don't know. – sniperd Jun 13 '18 at 14:56
  • @sniperd SQL Server 2014 - 12.0.4100.1 – Carlos Eduardo Teixeira Jun 13 '18 at 15:02
  • Is the issue that you'll have to dynamically make the sql (columns and values), or that you don't want to do 20 million separate transactions? – sniperd Jun 13 '18 at 15:06
  • @sniperd im trying to save as most time as i can while using the db, i dont mind doing a paginated transaction of 100 by 100 lines, but i need to insert the 27 coluns with values every insert made, i have the arrays of coluns and values being mounted correctly, i just need to insert them at once – Carlos Eduardo Teixeira Jun 13 '18 at 15:12

1 Answers1

0
var columns = [ 1, 2, 3, 4 ...]
var values = [a, b, c ,4 ...]
request.query(`INSERT INTO TABLE (${columns}) VALUES ?`), [[values]])


columns is an array so will have convert into a string for removing '[' and ']' brackets.

Nitesh
  • 1
  • 2