[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