# Bulk insert into SQL using system.db.runPrepUpdate

**URL:** <https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [August 4, 2023, 11:00am UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431 "2023-08-04T11:00:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pablo\_Fernandez\_Expo](https://avatars.discourse-cdn.com/v4/letter/p/c67d28/32.png) [@Pablo\_Fernandez\_Expo](https://forum.inductiveautomation.com/u/Pablo_Fernandez_Expo)\
**Post date:** [August 4, 2023, 11:00am UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431/1 "2023-08-04T11:00:51Z")

</div>

Hi,

I am using system.net.httpGet() to fetch weather data and writing it to a table in SQL server. Manage to get it working by inserting the data received 1 by 1, but i cannot figure it out to do it with just 1 insert. Any idea how to do it? It is even possible?

The below code works for me:

```auto
	import json
	# get the json weather response from the NOAA.
	lat = "38.6524"
	lng = "-121.1896"
	url = "https://api.weather.gov/points/%s,%s" % (lat, lng)
	noaaResponse = system.net.httpGet(url)
	noaaJSON = system.util.jsonDecode(noaaResponse)
	
	# Find the forecast URL.
	properties = noaaJSON["properties"]
	forecastURL = properties["forecast"]
	
	# Get the forecast from NOAA.
	forecastResponse = system.net.httpGet(forecastURL)
	forecastJSON = system.util.jsonDecode(forecastResponse)
	
	# Print out the forecast in a human-readable way.
	periods = forecastJSON["properties"]["periods"]
	
	# Define the SQL query to insert the data into the table
	sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES (?, ?, ?)"
	
	#Convert JSON to string if you want to save it in a column later. OPTIONAL.
	#jsonstring = json.dumps(forecastJSON)
	
	#Loop and insert rows 1 by 1
	for data in periods:
	    period_name = data["name"]
	    temperature = str(data["temperature"]) + " °F"
	    detailed_forecast = data["detailedForecast"]
	    args = [period_name, temperature, detailed_forecast]
	    system.db.runPrepUpdate(sql, args, "SQLConnection")

```

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [August 4, 2023, 11:25am UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431/2 "2023-08-04T11:25:25Z")

</div>

Yes, it's certainly possible. You'll want to build your insert string in your for loop and do the runPrepUpdate after the loop. You'll want to be mindful of your SQL transaction limits (ex. 1000 records per insert for SQL Server).

```auto
sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES " 
for data in periods:
    sql += "(?, ?, ?)," 
    # do other stuff
system.db.runPrepUpdate(sql[:-1], args, "SQLConnection")

```

---

<div class="post-metadata">

**Author:** ![Pablo\_Fernandez\_Expo](https://avatars.discourse-cdn.com/v4/letter/p/c67d28/32.png) [@Pablo\_Fernandez\_Expo](https://forum.inductiveautomation.com/u/Pablo_Fernandez_Expo)\
**Post date:** [August 4, 2023, 11:58am UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431/3 "2023-08-04T11:58:36Z")

</div>

Got this error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 4.

I think "args" values are not being accumulated in the loop and just sending 3 values.

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [August 4, 2023, 12:42pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431/4 "2023-08-04T12:42:24Z")

</div>

Right, you'll need to define args outside of the loop and append each row value. You're creating 1 long insert string, so you need to do the same for the "?" argument values.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [August 4, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-into-sql-using-system-db-runprepupdate/77431/5 "2023-08-04T14:33:45Z")

</div>

You can build the args list and then use that length to join your place holder string as needed.

Do know that there is a limit to the number of place holders that JDBC will accept, IIRC its 2500.

```auto
sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES {}"
args = []
for data in periods:
	args.extend([data['name'], "{} °F".format(data['temperature']),data['detailedForecast']])

system.db.runPrepUpdate(sql.format(','.join(['(?,?,?)'] * len(args)/3)),args,'SQLConnection')

```

Note the use of extend here as opposed to append. the args needs to be a simple list, not a list of lists.