Electronic Archive with Insert into Parent Table

  • 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

  • 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)

  • 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.

  • 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.

  • 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]

  • 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

  • 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

  • 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