How to load a Foreign Key Column

Question

Ok guys here is what I going through, I have a parent table called [dbo].[FileMetaData] and a child table called [dbo].[Ref_FileType] which is a reference to the Parent table. In the child table I have just two columns, TypeId whis is FK in the Parent table and TypeDescription I want to popluate the TypeId column in the parent table [dbo].[FileMetaData], here is how is got the typeDescription in the child table

INSERT into [test].[dbo].[Ref_FileType] (TypeDescription)
( select Distinct RIGHT(s.FileName,4) from [test].[dbo].[FileMetadata]s  
WHERE NOT EXISTS (SELECT * FROM [test].[dbo].[Ref_FileType] ))

example is

TypeID | Type Descritption
    1        xlsx
    2        txt
    3        TCF

Now I want to popluate the parent table [dbo].[FileMetaData] with and allocated TypeIdin the child table [dbo].[Ref_FileType] also note that I got the TypeDescription from the insert statement above. So what I want is to insert based on the last 4 char in the file name, and example DataValidationRules.xlsx.

If you check it it is a FileMetaData, so if it Fileaname ends with xlsx. I want to populate all typeID for such file with 1, if it ends with txt =2 , e.t.c

Please Kindly give a clear answer.

Thanks.

How is `[dbo].[Fileinformation]` related with the `[dbo].[FileMetaData]` or with the child table ? — praveen, Apr 30 '13 at 05:46
Am I right, that you just want to set `TypeId` for each `FileMetaData` based on the file extension? — outcoldman, Apr 30 '13 at 06:54
@ outcoldman Yes you are right, that's exactly what I want to do. — user2183502, Apr 30 '13 at 12:22
@Praveen, I just edited the question now, I made a mistake with the dbo.fileInformation. — user2183502, Apr 30 '13 at 12:28
I don't understand your design. I would have thought that table table 'Ref_FileType' should be the referenced table and table 'FileMetaData' should be the referencing table with a FK to 'Ref_FileType'. — Ɖiamond ǤeezeƦ, Apr 30 '13 at 12:44

Ɖiamond ǤeezeƦ · Accepted Answer · 2013-05-01T08:52:45.100

0

Based on your description, I am assuming that you have a table structure something like:

CREATE TABLE FileMetaData
(
  FileName varchar(256) NOT NULL,
  FileTypeId int NULL
);

CREATE TABLE Ref_FileType
(
  TypeId int NOT NULL IDENTITY,
  Description varchar(4) NOT NULL
)

And that the table is populated with some data like:

INSERT INTO FileMetaData VALUES ('DataValidationRules.xlsx', NULL);
INSERT INTO FileMetaData VALUES ('Readme.txt', NULL);
INSERT INTO FileMetaData VALUES ('SomeFile.TCF', NULL);

You can extract the file extensions using the following statement, taking variable-length extensions int account:

WITH temp (Description) AS
(
  SELECT DISTINCT SUBSTRING(FileName, StartPosition, LEN(FileName) - StartPosition + 2)
  FROM
  (
    SELECT FileName, LEN(FileName) - CHARINDEX('.', REVERSE(FileName)) + 2 AS StartPosition
    FROM FileMetadata
  ) T1
)
INSERT INTO Ref_FileType (Description)
SELECT T1.Description
FROM temp T1
LEFT JOIN  Ref_FileType T2 ON T2.Description = T1.Description
WHERE T2.TypeId IS NULL;

The next step is to associate each row in the FileMetaData table with the correct file type:

UPDATE T1
SET FileTypeId = T2.TypeId
FROM FileMetaData T1
JOIN Ref_FileType T2 ON T2.Description = SUBSTRING(FileName, LEN(FileName) - LEN(T2.Description) +1 , LEN(T2.Description));

Hope this helps.

SQL Fiddle: http://sqlfiddle.com/#!3/50f0d/5

edited May 01 '13 at 08:52

answered Apr 30 '13 at 12:58

Ɖiamond ǤeezeƦ

3,223
3
28
40

@user2183502 I've updated my answer as it caused an error when there were two or more files with the same extension. Also modified to handle extensions of different lengths. If I've answered your question please mark my answer as accepted. Thanks. – Ɖiamond ǤeezeƦ Apr 30 '13 at 13:44
what other way can i extract the file extensions, The logic I used before seem not to work too well, I tried splitting the file name with last period to get a proper file extension, Can you help with this? Thanks – user2183502 May 01 '13 at 03:51
@user2183502 see my answer above. It finds the last period and extracts all characters to the right of the period as the extension. However, it will fail for file names that do not have an extension (see here for an idea of how you might fix it: http://stackoverflow.com/questions/9479843/tsql-last-occurrence-of-inside-a-string). – Ɖiamond ǤeezeƦ May 01 '13 at 08:56
@ Diamond, i have this issue can you help out? working on this task I used the following trim statement to populate different table an example is select Distinct rtrim(right(FilePath, charindex('\', reverse(FilePath)) - 1)) from [test].[dbo].[FileMetadata]s to pull the last string after the \ in my table now the string I need to pull is the ABC in this column \\doc\dfs\SCPD\Metadata\MetaData_Creation_Process\Members\ABC\SELL How do I go about this? Thanks for your help?.. – user2183502 May 06 '13 at 18:04
@user2183502 To help you get an answer quicker (sorry I'm a busy at the moment), post this as another question with a link to this question. Other users can then help you too! – Ɖiamond ǤeezeƦ May 09 '13 at 11:49

How to load a Foreign Key Column

1 Answers1