Append and Update a SQL database with gateway event script

General Discussion
1

I need to update & append multiple rows of data from a dataset to a SQL Server Database.

If I have a variable # of rows, how do I iterate through them all and update and/or append predicated upon whether they exist within the database.

Thanks

2

That can get complicated fast. Is there a lot of rows in your source dataset?

You can do it a couple ways. You can do a for loop to cycle through your source dataset and use one of the columns data to do a system.db.runScalarPrepQuery to find out if the row already exist and store the ID for use in your update query. Using an if/else statement you build up 2 datasets, 1 for updates and 1 for new rows. After you build your datasets you would need to check to see if they have data in them, if they do you would run your update and/or insert queries. For your update query I have never tried to update multiple rows at once so I’m not sure how to do it. So you may have to loop through your dataset row by row running your update query for each row in the dataset. For the insert query though you can dynamically build your insert query to insert multiple rows at once. I’m attaching an example of how I have done it for inserts in the past.

i = 0
columns = "Col1,Col2,Col3,Col4,Col5,Col6"
data = ""

#create string for sql insert statement
for row in dataSet:
	if i == 0:
		i += 1 
		data = "('" + row[1] +"','" + row[2] + "','" + row[3] + "','" + row[4] + "','" + row[5] + "','" + row[6] + "')"
	else:
		i += 1
		data = data + ",('" + row[1] +"','" + row[2] + "','" + row[3] + "','" + row[4] + "','" + row[5] + "','" + row[6] + "')"
		
#create SQL insert statement
query = "INSERT INTO table_name (" + columns + ") VALUES "+ data 
		
#if data is available to insert, run insert query
if i != 0:
	system.db.runUpdateQuery(query, 'DatabaseName')

Another option is running your query on your destination table at the start of your script then looping through both datasets to compare a value between each of them. Using this compare for your if/else statement I talked about above. This will limit the number of database queries you have to do but if you have a lot of data in the two tables it can be a big load to loop through them. At the least you would want to use breaks in your script to limit the loops where you can but even with that, large datasets can be a challenge.

Another idea would be to do one insert into a holding table in your database. Then call a stored procedure in your database to do your update/inserts in order to limit the number of queries you have to do. This would be my preferred method since it would allow you do it without big loops and without doing multiple queries.