Edit : This post is outdated. EDT Version 2.3 and above includes new task Types 'Edit data' and Edit/Insert data' that automatically generates update/insert statements from the primary key properties of the table.One question that pops up with the current version is how do I UPDATE existing data?
This will be much easier with the new version 2.0 (coming soon) but meanwhile we can show you how to do this.
We need to add some custom SQL into the task settings.
Lets say we have a simple sheet of data, with a key column Customer_ID and Customer Name.
Some of the rows will have an empty Customer_ID (assuming the table is set up to auto increment the ID for new customers). These rows represent new customers we need to INSERT.
Some of the rows will have a customer ID populated, these rows we need to UPDATE.
Out custom SQL needs to look up and test for the existence of a customer ID then perform the correct action.
Here is an example of the custom SQL required for SQL Server:
Code:
IF (SELECT COUNT(*) FROM Customers WHERE Customer_ID = {XL-merge:Customer_ID}) > 0
UPDATE Customer_ID SET Name = ‘{XL-merge:Name}’
WHERE Customer_ID = {XL-merge:Customer_ID}
ELSE
INSERT INTO Customers ([Name])
VALUES ('{XL-merge:Name}')
If you need any help with similar problems or other Custom SQL - please do ask us - we are SQL Experts :)
Edited by user Sunday, January 06, 2013 9:45:45 AM(UTC)
| Reason: Outdated post