A database (classic parent-Child ID Association) update title _mssql

Yesterday, the incident came together a database topic, which is the classic parent-child ID in the same database table design type. You need to add a field to the original table, and then insert the parent-child details of the node into a new field in the original table, as shown in the following illustration.

AreaCode, AreaName, Parentcode (original table three fields). Content __ New fields, update all the parent menu information under the AreaCode to the Content field added to the original table, and use the Red Line box (meaning to be clear.)

AreaCode: Region ID areaname: Regional Introduction Parentcode: Parent AreaCode (Content---to spell all the parent AreaCode under this areaname: Yuecheng District, Shaoxing, Zhejiang Province string insert)

Before update:

After the update:

In the project, relative to this kind of content, typically stored in memory, as a cache to avoid frequent connection to the database, resulting in performance problems.

The code is not on, simple recursion can be achieved.

1 Remove all the contents of the table from the database first, and cache them. (This table is almost 3000 data)
2 again write a recursive function, according to each incoming AreaCode (the first step cached database out of the whole table set can be converted to dictionary<int,arearegion> type), get its parentcode value, as long as Parentcode is not 0 (that is, not the top-level node, Parentcode is the top-level node), then continue to call the function, passing in the Parentcode information of this query entity.
3 in the code is very convenient to detect the parent node all information, and then Update to the database.

Thanks to Chinghaiyampo Grayboy can complete through common table expressions

The Code is as follows:

With CTE (areacode,areaname,content) as
(
---query parentcode is 0 (Parentcode is the top-level menu for 0)
Select Areacode,areana Me,cast (areaname as varchar) as content from
[arearegion] where parentcode=0
UNION ALL
--and then recursively query 
Select A.areacode,a.areaname,cast (a.content+ ', ' +b.areaname as varchar () as content 9 from [arearegion] a INNER join C Te b on A.parentcode=b.areacode)
select * FROM CTE

---landlord thought (I think it's complicated SB)

1 landlord, the first thought is that the use of cursors to store whole table records.

The 2 cursor obtains the AreaCode of one record at a time, and then finds all its parent tables (including its own) information through the AreaCode, and finally, a field of the table that is queried is spelled as a string.

Insert Table variable (two fields are: AreaCode, Content).

3 Finally, the table variable defined is updated with the original table by INNER JOIN (the AreaCode field is inserted in the table variable).

The update script is specific as follows:

Use JKCRM Go--Defines the cursor DECLARE updatecursor CURSOR SCROLL for SELECT A.areacode to the from DBO. Arearegion A--Open cursor open updatecursor--Define variable store sequentially get cursor value DECLARE @aID NVARCHAR = '---Define variables store parent node specific information DECLARE @pStr Nvarch
AR (300) = '; --Define table variables store two fields AreaCode content: All parent menu information for this field DECLARE @TempTable table (AreaCode INT PRIMARY KEY, content Nvarc   HAR (3000))--first fetch the first value of the cursor inserts a variable @AID FETCH a updatecursor into @AID while (@ @FETCH_STATUS =0) BEGIN--print (@AID)
; ---query all its parent menu information with TB as (---recursive query parent-child menu information Common table expression recursive query SELECT a.*,0 as level from JKCRM through incoming @AID. Dbo. Arearegion a WHERE a.areacode= @AID UNION all SELECT b.*, level+1 as level from TB A INNER JOIN Jkcrm. Dbo. Arearegion B on A.parentcode=b.areacode)---and then concatenate the table information of the query into a string this is a Select loop query Select @pStr = @pStr + case When @pStr = ' THEN TB. AreaName ELSE ', ' +TB. AreaName end to TB order by TB. Level ASC--PRINT (@pSTR)--Inserts a table variable insert into @TempTable SELECT @aID, @pStr---Set empty SET @pStr this node obtained through AreaCode @pStR= ' FETCH NEXT from updatecursor to @AID end--select B.areacode,b.areaname,b.parentcode,a.content,a.areacode--FROM @TempTable A right JOIN DBO. Arearegion B on A.areacode=b.areacode ORDER BY B.areacode---here for update update operations update A SET a.content=b.content FR OM DBO. Arearegion A INNER JOIN @TempTable B on A.areacode=b.areacode--Turn off the release cursor close updatecursor deallocate updatecursor

Part of script SQL from the original table

/* Navicat SQL Server Data Transfer source server:sql source Server version:120000 Source Host:.: 1433 Sou RCE DATABASE:JKCRM Source schema:dbo target server type:sql server target server version:120000 File Enco ding:65001 date:2015-06-12 11:20:40 */--------------------------------Table structure for arearegion------ ------------------------DROP TABLE [dbo].
[Arearegion] Go CREATE TABLE [dbo]. [Arearegion] ([areacode] varchar NOT NULL, [areaname] varchar NULL, [parentcode] varchar (a) null, [Content] nvarchar (200) NULL) go--------------------------------Records of arearegion------------------------------inserts into [dbo].[ Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110000 ', n ' Beijing ', n ' 0 ', null) go to INSERT into [db O]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110100 ', n ' Dongcheng ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [parenTcode], [Content]) VALUES (n ' 110200 ', n ' Xicheng ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110300 ', n ' Chongwen District ', N ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110400 ', n ' Xuanwu ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110500 ', n ' Chaoyang ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110600 ', n ' Fengtai ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110700 ', n ' Shijingshan ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110800 ', n ' Haidian ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 110900 ', n ' Mentougou district ', N ' 110000 ', null) go to INSERT into[dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111000 ', n ' Fangshan ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111100 ', n ' Tongzhou District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111200 ', n ' Shunyi District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111300 ', n ' Changping District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111400 ', n ' Daxing District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111500 ', n ' Huairou ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111600 ', n ' Pinggu District ', n ' 110000 ', null) go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (N ' 1117', n ' Miyun ', n ' 110000 ', null ' go to INSERT into [dbo]. [Arearegion] ([AreaCode], [AreaName], [Parentcode], [Content]) VALUES (n ' 111800 ', n ' Yanqing County ', n ' 110000 ', null)