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

[Borge Hansen]

Hi all,

It appears that this article addresses the issue:

https://www.simple-talk.com/sql/t-sql-programming/
concatenating-row-values-in-transact-sql/

Posting it here in case others may run into the same problem.

/borge

On Tue, Nov 8, 2016 at 10:06 PM, Borge Hansen <pcs.accessd at gmail.com> wrote:

> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>