Prev: query perfomance issue with Select statment in the Select list
Next: To update a clr dll should I drop the assembly?
From: shapper on 1 Jul 2008 11:33
Hello,

I have 3 tables:

create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
Path nvarchar(800) not null
)
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
on delete cascade
)

I create a table from a list of Tags (CSV) having these table only
their names.

For each of this Tags I want to:
1. If it already exists in Tags then get its ID and added it to
FilesTags with a given FileID.
2. If it doesn't exist in Tags then create it, generating a new ID
(Guid), and after it do (1).

How can I do this?

Thanks,
Miguel
From: John Bell on 5 Jul 2008 12:40

"shapper" <mdmoura(a)gmail.com> wrote in message
news:d6751bbf-6f1c-42b4-a0ef-e662a72b92b6(a)m73g2000hsh.googlegroups.com...
> Hello,
>
> I have 3 tables:
>
> create table dbo.Files
> (
> FileID uniqueidentifier not null
> constraint PK_File primary key clustered,
> Path nvarchar(800) not null
> )
> create table dbo.Tags
> (
> TagID uniqueidentifier not null
> constraint PK_Tag primary key clustered,
> [Name] nvarchar(100) not null
> )
> create table dbo.FilesTags
> (
> FileID uniqueidentifier not null,
> TagID uniqueidentifier not null,
> constraint PK_FilesTags
> primary key clustered (FileID, TagID),
> constraint FK_FilesTags_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesTags_Tags
> foreign key(TagID)
> references dbo.Tags(TagID)
> on delete cascade
> )
>
> I create a table from a list of Tags (CSV) having these table only
> their names.
>
> For each of this Tags I want to:
> 1. If it already exists in Tags then get its ID and added it to
> FilesTags with a given FileID.
> 2. If it doesn't exist in Tags then create it, generating a new ID
> (Guid), and after it do (1).
>
> How can I do this?
>
> Thanks,
> Miguel
>

Hi Miguel

Assuming tags are in a table called Loaded_Tags and each name is unique, and
the file exists in Files and each path is unique
and is currently held in a variable @Path

-- Add tags that are not already there
INSERT INTO dbo.Tags [Name]
SELECT L.[NAME]
FROM dbo.Loaded_Tags L
LEFT JOIN dbo.Tags T ON T.[Name] = L.[Name]
WHERE T.TagId IS NULL

-- Add tags from file with current file
INSERT INTO dbo.FilesTags ( TagId, FileId )
SELECT T.TagID, F.FileId
FROM dbo.Loaded_Tags L
JOIN dbo.Tags T ON T.[Name] = L.[Name]
CROSS APPLY dbo.Files F
WHERE F.Path + @Path


As you have a clustered index on your GUIDs you should use the
newsequentialid function to generate them, this will reduce fragmentation.
The use of a GUID will use more space than if you used an identity.


create table dbo.Files
(
FileID uniqueidentifier not null default newsequentialid()
constraint PK_File primary key clustered,
Path nvarchar(800) not null constraint uq_files unique
)

create table dbo.Tags
(
TagID uniqueidentifier not null default newsequentialid()
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null constraint uq_taq unique
)

create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
on delete cascade
)

John

 | 
Pages: 1
Prev: query perfomance issue with Select statment in the Select list
Next: To update a clr dll should I drop the assembly?