November 30, 2014 at 10:33 pm
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
November 30, 2014 at 11:22 pm
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
Change is inevitable... Change for the better is not.
November 30, 2014 at 11:33 pm
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.
December 1, 2014 at 12:29 am
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.
December 1, 2014 at 7:11 am
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]
December 2, 2014 at 10:02 pm
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
December 3, 2014 at 1:15 am
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
December 3, 2014 at 2:30 am
Thank you sooooooo much for your reply.my problem is solved by your reply.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy