Create child table records

Transact-SQL
1

Hello,

I have an old database table as follows:

create table dbo.Books_OLD ( 
  Id int identity not null constraint PK_Books_OLD_Id primary key (Id),
  Title nvarchar (200) not null,
  ImageContent varbinary (max) null, 
  ThumbnailContent varbinary (max) null
) 

insert into dbo.Books_OLD (Title, ImageContent, ThumbnailContent) values ('Title 1', 0x11223344, 0x11223344);
insert into dbo.Books_OLD (Title, ImageContent, ThumbnailContent) values ('Title 2', null, 0x11223344);
insert into dbo.Books_OLD (Title, ImageContent, ThumbnailContent) values ('Title 3', 0x11223344, null);
insert into dbo.Books_OLD (Title, ImageContent, ThumbnailContent) values ('Title 4', null, null);

And I have a new database with the following tables:

create table dbo.Books ( 
  Id int identity not null constraint PK_Books_Id primary key (Id),
  Title nvarchar (200) not null 
)    

create table dbo.Files (
  Id int identity not null constraint PK_Files_Id primary key (Id),
  Content varbinary (max) null,
  Name nvarchar (280) null
)

create table dbo.BookFiles (
  BookId int not null, 
  FileId int not null, 
	constraint PK_BookFiles_Id primary key (BookId, FileId)
)

alter table dbo.BookFiles
add constraint FK_BookFiles_BookId foreign key (BookId) references Books(Id) on delete cascade on update cascade,
	constraint FK_BookFiles_FileId foreign key (FileId) references Files(Id) on delete cascade on update cascade;

I am adding books and files to these tables from an old database:

insert into MyDb.Books (Title)
SELECT
   Title,
   ImageContent,
   ThumbnailContent 
FROM MyDb.Books_OLD

For each new book I need:

  1. Check if ImageContent is null.
    If it is not null then create a new BookFile / File.
    The ImageContent will be File.Content and "Image" will be File.Name.

  2. Check if ThumbnailContent is null.
    If it is not null then create a new BookFile / File.
    The ThumbnailContent will be File.Content and "Thumbnail" will be File.Name.

How can I do this?

Thank You

2

The logic is:

  1. Insert the Book row
  2. If applicable, insert the File row for ImageContent. Then insert a corresponding BookFiles row.
  3. If applicable, insert the File row for ThumbnailContent. Then insert a corresponding BookFiles row.

An alternative, that can be more easily written purely in SQL and is more set oriented, is:

  1. Load all the Book and File rows.
  2. Do a second pass of the old table, joining to Books and Files, and insert the BookFiles rows.

Btw, will the same File be shared between Books? If so, the current design is OK. But the loading is more complex than you suggest, because you first have to search the file content to see if the new file being loaded matches a file already loaded.

If not, if a given File row is only ever for a single boo, then the File table should have a Book FK in it, you don't need a separate table -- unless there is intersection data relevant only to the (BookId, FileId) combination which you're not showing just for ease of display / convenience.

3

Btw, will the same File be shared between Books? If so, the current design is OK. But the loading is more complex than you suggest, because you first have to search the file content to see if the new file being loaded matches a file already loaded.

In theory a file should not be shared by two books but in the future it could be.

But that was not the main reason why I have one Files table and a many to many relationship table:

Books table is not the only one that has Files. There are many others ... So instead of having one File table for each one and having a FK I decided to create a common Files table that holds all files of the application.

Sure in some cases it is logic that one File can only be used by one entity but in other cases Files can be shared.

So this solution becomes more flexible to me ... What do you think?

An alternative, that can be more easily written purely in SQL and is more set oriented, is:
Load all the Book and File rows.
Do a second pass of the old table, joining to Books and Files, and insert the BookFiles rows.

Could you provide me a Code example? I am not sure how to do this ...

4

That's perfect, you have the right reasons to create a separate table.

I'd do it like this, just so I could use straightforward, basic SQL statements to load all the tables. But there are lots of ways to do this load.

ALTER TABLE dbo.Files Add SourceId int null;

SET IDENTITY_INSERT dbo.Books ON;
INSERT INTO dbo.Books ( Id, Title )
SELECT Id, Title
FROM dbo.Books_OLD;
SET IDENTITY_INSERT dbo.Books OFF;

INSERT INTO dbo.Files ( Content, SourceId )
SELECT CA.Content, BO.Id
FROM dbo.Books_OLD BO
CROSS APPLY (
    VALUES(ImageContent, ThumbnailContent)
) AS CA(Content)
WHERE CA.Content IS NOT NULL;

INSERT INTO dbo.BookFiles ( BookId, FileId )
SELECT F.SourceId, F.Id
FROM dbo.Files F;

UPDATE dbo.Files --/*or*/ ALTER TABLE dbo.Files Add SourceId int null;
SET SourceId = null;