[dba-SQLServer] [AccessD] Cross Posted - Moving a Access Update Query to MS SQL

[Borge Hansen]

Hi all,

MS Access 2010
SQL 2014 Express

I have trouble in moving a simple Access update query to MS SQL Server
syntax

To exemplify: two tables:

tblMerged
UFN  (PK, Number / Integer)
MergedData (Memo)

Sample Data:
10,
20,
30


tblSource
ID (PK, Number / Integer)
UFN (FK, Number / Integer)
SourceData (Text, 255)
SelectFlag (Boolean)

Sample Data:
ID    UFN    SourceData    SelectFlag
15    10    10_Text_1    False
14    10    10_Text_2    False
3    10    10_Text_3    False
40    20    20_Text_1    False
39    20    20_Text_2    False
11    20    20_Text_3    False
70    30    30_Text_1    False
68    30    30_Text_2    False
65    30    30_Text_3    False
101    10    10_Text_0    False
102    20    20_Text_0    False
103    30    30_Text_0    False


The Update Query looks like this:

UPDATE tblMerged AS m
INNER JOIN tblSource AS s ON m.UFN = s.UFN
SET m.MergedData =
IIf(Len([m].[MergedData])>0,
    [m].[MergedData] & Chr(13) & Chr(10) & [s].[SourceData] & "  >>A  " &
"  >>B  " & "  >>C  ",
    [s].[SourceData] & "  >>A  " & "  >>B  " & "  >>C  "),
s.SelectFlag = True
WHERE (((s.SelectFlag)=False));


The resulting data set in tblMerged looks like:
UFN    MergedData
10    "10_Text_1  >>A    >>B    >>C
         10_Text_2  >>A    >>B    >>C
         10_Text_3  >>A    >>B    >>C
         10_Text_0  >>A    >>B    >>C  "
20    "20_Text_1  >>A    >>B    >>C
         20_Text_2  >>A    >>B    >>C
         20_Text_3  >>A    >>B    >>C
         20_Text_0  >>A    >>B    >>C  "
30    "30_Text_1  >>A    >>B    >>C
         30_Text_2  >>A    >>B    >>C
         30_Text_3  >>A    >>B    >>C
         30_Text_0  >>A    >>B    >>C  "


So, for each record in tblMerged the query iterates over all inner-joined
records in tblSource and neatly concatenates all SourceData text strings
with a linefeed/linebreak between each text string
AND it updates the second table tblSource as well by setting SelectFlag to
True ....

(As an aside: no matter how I set the  sort index of the tblSource.ID - or
use a query as the second object in the update query - a query on tblSource
with a certain sort order on the records - the resulting text strings in
MergedData are always read in and merged in the order the records were
added.I would have expected same order as the PK index order of
tblSource.ID)


The question I am seeking your help with is How do we do this as a stored
procedure in MS SQL : concatenate and merge in the tblMerged and update the
SelectFlag in tblSource using one update query ??

I created the update query in a stored procedure in MS SQL as:

UPDATE dbo.tblMerged
SET [MergedData] =
IIF((LEN(cast([MergedData] as nvarchar(max))))> 0 ,
    CONCAT(cast([MergedData] as nvarchar(max)) , char(13), char(10) ,
cs.SourceData, '  >>A  ' ,  '  >>B  ' ,  '  >>C  '),
    CONCAT([cs].[SourceData] , '  >>A  ' , '  >>B  ' , '  >>C  '))
FROM dbo.tblMerged csm
JOIN tblSource AS cs ON csm.UFN = cs.UFN
;


The update query appears to only act on one record from tblSource
- and we are not able to update the SelectFlag - (hence no WHERE
(((s.SelectFlag)=0)) clause in the MS SQL )

The result of the update query on tblMerged:

UFN    MergedData
10    10_Text_2  >>A    >>B    >>C
20    20_Text_0  >>A    >>B    >>C
30    30_Text_3  >>A    >>B    >>C


A simple thing to do in Access SQL/Query - in MS SQL Server ???

Any help appreciated...

/borge