How to concatenate exiting data present in a variable with new data in a SQL Server table?

Question

I was wondering is there a way to concatenate a variable with incoming data. I currently have a table called TABLE-A with address variable which already has data in it. There is a front-end application through which users input information into this table. I was wondering is there a way to concatenate new incoming information with existing information. Below is the example.

Example:

TABLE-A (ORIGINAL VIEW)

Column A	Column B
JOHN	12 DOWNING STREET

TABLE-A (EXPECTED VIEW AFTER USER ENTERS NEW INFORMATION)

Column A	Column B
JOHN	12 DOWNING STREET; 24 WASHINGTON SQUARE PARK;

In the expected view '24 WASHINGTON SQUARE PARK' is the new address user enters and in the table we are capturing it and adding it to the existing address '12 DOWNING STREET'. I tried to create a trigger but our front end is not accepting a table with triggers enabled. So triggers are not an option.

It would be great if someone could provide any type of solutions.

Thanks, Banana19

Answer 1

If you need to update anything in database, just use update (you can add additional spaces/delimiters in SET part):

UPDATE TABLE-A SET [COLUMN B] = [COLUMN B] + NEW INFORMATION
WHERE [COLUMN A] = USER

But if the user deletes something through UI, it will be a problem. In my opinion, the approach should be different here. If it is a one column in database, UI should return a full line of current adresses. So you will need update a respective row with a line from UI without any manipulations.

Or there is another way - to keep all adresses separately. The structure will be like that:

Column A	Column B
JOHN	12 DOWNING STREET
JOHN	24 WASHINGTON SQUARE PARK

It is easy to manage data in such format (insert/update/delete/select/search).