Electronic Archive with Insert into Parent Table

Question

Electronic Archive with Insert into Parent Table

elham_azizi_62

SSCertifiable

Points: 5095
More actions
November 30, 2014 at 10:33 pm

#298998

hi everybody.
I want to insert folder and subfolder an files from directory to Two Parent Table. for reading folders I used this script:
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'E:\ElectronicArchieve',0,1;
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id
)
FROM #DirectoryTree d;
WITH CTE as (
SELECT
Id, CAST(SubDirectory as nvarchar(255)) as SubDirectory,
Depth, ParentDirectory, CAST('' as nvarchar(255)) as Parent,isfile,flag
FROM #DirectoryTree WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id, CAST(d.SubDirectory as nvarchar(255)),
d.Depth, d.ParentDirectory, CAST(CTE.SubDirectory as nvarchar(255)),d.isfile,d.flag
FROM #DirectoryTree d
INNER JOIN CTE ON d.ParentDirectory = CTE.Id
)
SELECT * FROM CTE order by id
Now I want to insert folders and subfolders into this parent table:
CREATE TABLE [dbo].[ArchiveDepartment](
[SubjectID] [int] IDENTITY(1,1) NOT NULL,
[SubjectName] [nvarchar](1024) NOT NULL,
[SubjectDescription] [nvarchar](1024) NULL,
[CreationDate] [datetime] NOT NULL,
[ParentID] [int] NOT NULL,
[OrganizationID] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
CONSTRAINT [PK_ArchiveDepartment] PRIMARY KEY CLUSTERED
(
[SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ArchiveDepartment] ADD CONSTRAINT [DF_ArchiveDepartment_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[ArchiveDepartment] ADD CONSTRAINT [DF_ArchiveDepartment_OtherPolicyTypeID] DEFAULT ((1)) FOR [OtherPolicyTypeID]
GO
and I want to insert files into this parent table:
CREATE TABLE [dbo].[Rel_ArchiveDepartment_Entities](
[SubjectID] [int] NOT NULL,
[ArchiveTime] [datetime] NOT NULL,
[ArchiveDescription] [nvarchar](1024) NULL,
[EntityTitle] [nvarchar](4000) NULL,
CONSTRAINT [PK_Rel_ArchiveDepartment_Entities] PRIMARY KEY CLUSTERED
(
[SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rel_ArchiveDepartment_Entities] ADD CONSTRAINT [DF_Rel_ArchiveDepartment_Documents_ArchiveTime] DEFAULT (getdate()) FOR [ArchiveTime]
GO
in addition to say: SubjectId in Two tables are the same.
I want to know how I can do this.thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Are you allowed to use xp_CmdShell? That would probably make your life a bit easier here. And, no... I'm not suggesting that you enable a proxy for a given login. That's a bad thing to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 2

my major problem is inserting in to tables archivedepatment which stores folder and subfolders in order to hierarchical and inserting into rel_archivedepartment_entities which stores files in folders in archivedepartment.

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 3

I need a script to do this. I really have a terrible with this. please help me and give me a query. thanks a lot.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

Now I want to insert folders and subfolders into this parent table:
CREATE TABLE [dbo].[ArchiveDepartment](
[SubjectID] [int] IDENTITY(1,1) NOT NULL,
[SubjectName] [nvarchar](1024) NOT NULL,
[SubjectDescription] [nvarchar](1024) NULL,
[CreationDate] [datetime] NOT NULL,
[ParentID] [int] NOT NULL,
[OrganizationID] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
CONSTRAINT [PK_ArchiveDepartment] PRIMARY KEY CLUSTERED
(
[SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ArchiveDepartment] ADD CONSTRAINT [DF_ArchiveDepartment_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[ArchiveDepartment] ADD CONSTRAINT [DF_ArchiveDepartment_OtherPolicyTypeID] DEFAULT ((1)) FOR [OtherPolicyTypeID]
GO
and I want to insert files into this parent table:
CREATE TABLE [dbo].[Rel_ArchiveDepartment_Entities](
[SubjectID] [int] NOT NULL,
[ArchiveTime] [datetime] NOT NULL,
[ArchiveDescription] [nvarchar](1024) NULL,
[EntityTitle] [nvarchar](4000) NULL,
CONSTRAINT [PK_Rel_ArchiveDepartment_Entities] PRIMARY KEY CLUSTERED
(
[SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rel_ArchiveDepartment_Entities] ADD CONSTRAINT [DF_Rel_ArchiveDepartment_Documents_ArchiveTime] DEFAULT (getdate()) FOR [ArchiveTime]
GO
in addition to say: SubjectId in Two tables are the same.
I want to know how I can do this.thanks

elham_azizi_62 (11/30/2014)
my major problem is inserting in to tables archivedepatment which stores folder and subfolders in order to hierarchical and inserting into rel_archivedepartment_entities which stores files in folders in archivedepartment.

Looks like you need independent IDs for the two target tables and a foreign key from files (Rel_ArchiveDepartment_Entities) to folders ([ArchiveDepartment])

For example

CREATE TABLE [dbo].[Rel_ArchiveDepartment_Entities](

fileID [int] IDENTITY(1,1) NOT NULL,

[SubjectID] [int] NOT NULL,

[ArchiveTime] [datetime] NOT NULL,

[ArchiveDescription] [nvarchar](1024) NULL,

[EntityTitle] [nvarchar](4000) NULL,

CONSTRAINT [PK_Rel_ArchiveDepartment_Entities] PRIMARY KEY CLUSTERED

(

[FileID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

,CONSTRAINT [FK_Files_2_Dir] FOREIGN KEY ([SubjectID]) REFERENCES [dbo].[ArchiveDepartment] ([SubjectID])

) ON [PRIMARY]

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 5

ok. but main problem is inserting in to archivedepatment from EXEC master.sys.xp_dirtree @Path,0,1;

please give me a query for this.thanks

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

Based on my, may be not full, understanding of what you need

-- CREATE Archive

drop table [dbo].[Rel_ArchiveDepartment_Entities];

go

drop table [dbo].[ArchiveDepartment];

go

-- some cols made NULLable

CREATE TABLE [dbo].[ArchiveDepartment](

[SubjectID] [int] IDENTITY(1,1) NOT NULL,

[SubjectName] [nvarchar](1024) NOT NULL,

[SubjectDescription] [nvarchar](1024) NULL,

[CreationDate] [datetime] NULL,

[ParentID] [int] NULL,

[OrganizationID] [int] NULL,

[DepartmentID] [int] NULL,

CONSTRAINT [PK_ArchiveDepartment] PRIMARY KEY CLUSTERED

(

[SubjectID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ArchiveDepartment]

ADD CONSTRAINT [DF_ArchiveDepartment_CreationDate] DEFAULT (getdate()) FOR [CreationDate]

GO

CREATE TABLE [dbo].[Rel_ArchiveDepartment_Entities](

[fileID] [int] IDENTITY(1,1) NOT NULL,

[SubjectID] [int] NULL,

[ArchiveTime] [datetime] NULL,

[ArchiveDescription] [nvarchar](1024) NULL,

[EntityTitle] [nvarchar](4000) NULL,

CONSTRAINT [PK_Rel_ArchiveDepartment_Entities] PRIMARY KEY CLUSTERED

(

[fileID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Rel_ArchiveDepartment_Entities]

ADD CONSTRAINT [DF_Rel_ArchiveDepartment_Documents_ArchiveTime] DEFAULT (getdate()) FOR [ArchiveTime]

GO

ALTER TABLE [dbo].[Rel_ArchiveDepartment_Entities] WITH CHECK

ADD CONSTRAINT [FK_Files_2_Dir] FOREIGN KEY([SubjectID])

REFERENCES [dbo].[ArchiveDepartment] ([SubjectID])

GO

-- RESET Archive

truncate table [dbo].[Rel_ArchiveDepartment_Entities];

delete from [dbo].[ArchiveDepartment];

-- COLLECT data

--parameter

declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL

DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (

id int IDENTITY(1,1)

,subdirectory nvarchar(512)

,depth int

,isfile bit

, ParentDirectory int

,flag tinyint default(0));

INSERT #DirectoryTree (subdirectory,depth,isfile)

VALUES (@myPath,0,0);

INSERT #DirectoryTree (subdirectory,depth,isfile)

EXEC master.sys.xp_dirtree @myPath,0,1;

UPDATE #DirectoryTree

SET ParentDirectory = (

SELECT MAX(Id) FROM #DirectoryTree

WHERE Depth = d.Depth - 1 AND Id < d.Id)

FROM #DirectoryTree d;

/*

-- these are at root level

select * from #DirectoryTree

where parentdirectory is null;

*/

SET IDENTITY_INSERT [dbo].[ArchiveDepartment] ON;

-- change cols mapping as needed

insert [dbo].[ArchiveDepartment] ([SubjectID] ,[SubjectName], [ParentID])

select id ,subdirectory, ParentDirectory

from #DirectoryTree

where isfile = 0;

SET IDENTITY_INSERT [dbo].[ArchiveDepartment] OFF;

SET IDENTITY_INSERT [Rel_ArchiveDepartment_Entities] ON;

-- change cols mapping as needed

insert [Rel_ArchiveDepartment_Entities] ([fileID] ,[EntityTitle] , [SubjectID])

select id ,subdirectory, ParentDirectory

from #DirectoryTree

where isfile = 1;

SET IDENTITY_INSERT [Rel_ArchiveDepartment_Entities] OFF;

-- SEE files with full paths

WITH dirs as (

SELECT

SubjectId, CAST([SubjectName] as nvarchar(max)) as dpath

FROM [dbo].[ArchiveDepartment]

WHERE ParentID IS NULL

UNION ALL

SELECT

d.SubjectId, dpath +'\'+d.[SubjectName]

FROM [dbo].[ArchiveDepartment] d

INNER JOIN dirs ON d.ParentID = dirs.SubjectId

)

SELECT dirs.dpath, f.*

FROM [Rel_ArchiveDepartment_Entities] f

JOIN dirs on f.SubjectID = dirs.SubjectID

Hope it helps

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 7

Thank you sooooooo much for your reply.my problem is solved by your reply.