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

Borge Hansen pcs.accessd at gmail.com
Mon Nov 14 04:01:53 CST 2016


Sending this again.I'm still working on replicating a certain Access update query in MS SQL and this may have the interest of others on the list./borge 
Get Outlook for iOS

		_____________________________
From: Borge Hansen <pcs.accessd at gmail.com>
Sent: Thursday, November 10, 2016 2:26 pm
Subject: Re: [AccessD] Cross Posted - Moving a Access Update Query to MS SQL
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>, Access Developers discussion and problem solving <accessd at databaseadvisors.com>


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



















	


More information about the AccessD mailing list