Two Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Two Tables

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-01 : 10:09:41

Hello,

I have two tables A and B, both have two fields: ID (Guid) and Name.

Table A as 10 records only with Name defined.
Table B as 1000 records with ID and Name defined.

I need to fill the IDs of the records in table A as follows:

1. Given A.Name if it exists such name in B then get its ID.
2. Given A.Name if it does not exist in B then create it in B with A.Name and a new Guid and finally copy it to A.

How can I do this?

Thank You,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 10:42:43

"1. Given A.Name if it exists such name in B then get its ID."
what do you want to do with this step ? What to do with the ID after you got it ?

KH
[spoiler]Time is always against us[/spoiler]

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-01 : 11:03:11

After it I will put it in a Many To many relationship table.

I am trying to create this in SQL but after it I will code it in LINQ in C# 3.5. Linq is giving me some problems so I am trying to make this work in SQL first.

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-01 : 11:11:05

I will give the full view of my problem.

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
)

Then I have a list of Tags (CSV) which I have converted to a table.
But I have only their names.

The ones that exist in Tags I get their IDs and place them in FilesTags ... The ones that do not exist in Tags I create them and after it place it in FilesTags.

I would like to make this work in SQL so I can later try to see if this is also possible with Linq.

Any help would be great.

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 13:32:14

will you be having FileID/Name also in csv? else how will you be dtermining what value is to be inserted for FileID in FileTags for that Tag?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-01 : 15:05:37

In the CSV I don't have the FileID but I can use a parameter ... since this will be used in Linq:

FileID = @FileID

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-02 : 07:47:54

Please, anyone?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 14:19:50

Get the csv data onto a staging table let it be Tag_Staging which contain Name. then you can do like this:-

DECLARE @INSERTED_Tags table
(TagID uniqueidentifier,
[Name] nvarchar(100) not null)

INSERT INTO FilesTags
SELECT t.TagID,@FileID
FROM Tags t
INNER JOIN Tags_Staging ts
ON ts.[Name]=t.[Name] 

INSERT INTO Tags
OUTPUT INSERTED.TagID,INSERTED.Name INTO @INSERTED_Tags
SELECT NEWID(),ts.Name
FROM Tags_Staging ts
LEFT JOIN Tags t
ON ts.[Name]=t.[Name]
WHERE t.[Name] IS NULL

INSERT INTO FilesTags
SELECT TagID,@FileID
FROM @INSERTED_Tags

@FileID is parameter for sending fileid value

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-07-02 : 15:31:25

Thank You!

Cheers,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources