Originally, http://community.9cbs.net/expert/topic/3663/3663934.xml? Temp = .9100458
- Test data Create Table Project (ID INT, Name Nvarchar (20), PARENT_ID INT) Insert Project Select 1, 'All Project', Nullunion All SELECT 2, 'Item 1', 1Union All SELECT 3, 'Item 2', 1
Create Table Task (ID Int, Name Nvarchar (20), Outline Varchar (10)) Insert Task SELECT 1, 'Task 1', '1'Union All SELECT 2,' Task 1.1 ',' 1.1'Union All Select 3, 'Task 1.1.1', '1.1.1'Union All Select 4,' Task 1.1.2 ',' 1.1.2'Union All SELECT 5, 'Task 2', '2'Union All Select 6,' Task 2.1 ',' 2.1'Union all Select 7, 'Task 2.1.1', '2.1.1'Union all Select 8,' Task 2.1.1.1 ',' 2.1.1.1'Union all SELECT 9, 'Task 3', ' 3'Union All SELECT 10, 'Task 4', '4'Union All Select 11,' Task 4.1 ',' 4.1'go
/ * - Processing Requirements 1. Import the data of a Task table of a database to another database of Project Table and Temp1 table. The underlying relationship between the introduced data is no longer displayed by the outline, but through the Parent_ID and Project_ID. level. 2. Table TEMP1 PROJECT_ID is associated with the ID in the Project table, and the PROJECT table in the table Project_ID is the connection to the table itself ID field. 3. To import the bottom task of the task table (no sub-task) into the Temp1 table, his superior task is connected by Project_ID 4. Parent_ID imported from the Task Table - * /
/ * - Final achievement:
- ** Project table content id name parent_id ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- All items NULL2 item 1 13 Project 2 14 Task 1 25 Task 1.1 46 Task 2 27 Task 2.1 68 Task 2.1.1 79 Task 4 2
(The number of rows affects is 9 lines)
--Temp1 table content id name suppl_iD ------------------------------------------------------------------------------------------------------ - 1 Task 1.1.1 52 Task 1.1.2 53 Task 2.1.1.1 84 Task 3 25 Task 4.1 9 (5 lines of the number of rows affect) - * /
- Processing stored procedure create proc p_process @ parent_id int = 2asset NoCount OnDeclare @ID, @ Step Int, @ s nvarchar (1000)
- Get the new number in the Project table (because you don't know if the Project ID is identified by the field, use some judgment) SELECT @ Step = Ident_inCr ('Project'), @ id = Ident_current ('Project') @ Stepif @ STEPIF @ ID is nullselect @ id = ISNULL (Max (ID), 0) 1 from projectselect @ s = 'alter table #t address Int Id IDENTITY (' RTRIM (@ID) ',' RTRIM (ISNULL (@step 1)) ')'
- Generate Temporary Table SELECT NAME, PARENT_ID = @ PARENT_ID, OUTLINE INTO #T from Task Awhere EXISTS (SELECT * from Task Where Outline Like a.outline '.%') Order by Outline
- Generate ID and generate praent_idexec (@S) Update a set parent_id = B.IDFROM # ta, # t bwhere charindex ('.', A.outline)> 0and a.outline like B.outline '.%' AND Charindex ('.', a.outline, len (b.outline) 2) = 0
- Processing results Insert Projectif @Step is Not NullSet Identity_Insert Project OnInsert Project (ID, Name, Parent_id) Select ID, Name, Parent_id from #t
- Generate Temp1iF EXISTS (Select * from sysobjects where name = 'temp1' and Objectproperty (id, 'isusertable') = 1) DROP TABLE TEMP1SELECT ID = Identity (int), a.name, isnull (B.ID, @ PARENT_ID) AS Project_IDINTO TEMP1 from Task a Left Join #tb on a.outline like b.outline '.%' and charIndex ('.', a.outline, len (b.outline) 2) = 0where not exists (SELECT * from #t where outline = a.outline) Go - Call EXEC P_PROCESS 2
- Show the result after processing SELECT * from ProjectSelect * from Temp1go
- Delete Test Drop Table Project, Task, Temp1Drop Proc P_Process
