Insert Data Logic problems - 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)

Insert Data Logic problems

Author

Topic

solent
Starting Member

33 Posts

Posted - 2007-09-13 : 16:30:50

Hello,
Here is the logic hopefully someone will be able to clear this up for me.
I have a c# class that does some file indexing and then it inserts a record for each file (movie) it finds on the specific directories.
Now i also have a reg expression where i sometimes I am able to extract the name and year of the movie (depends on how hard the filename is). The reg expression is also able to get the number of disks of each movie if it’s made up of more than one file.
For example C:\Movies\300_CD1.avi C:\Movies\300_CD2.avi

No for such movie records i want to have a field that will identify them that are part of the same movie (other than the title because the title can have duplicates). Many movies are remakes with the same title.
The problem is that from my c# class I am inserting a record one by one after the file is scanned.
Actually I put all the indexed files in an arrayList and then insert them one by one.
How can i insert the same lm_mID key for every movie that has more than 1 parsedName?
Also the lm_mID key should be the PK ID in the movies table so after when I look for a movie and its files I could run a SELECT Inner Join on the two and get the movie with its details and the movie Files from the Local_movies table.
I hope I have explained it well.
Here is the DDL and also some test data
Local_Movies Table


CREATE TABLE [dbo].[Local_Movies](
	[lm_ID] [int] IDENTITY(1,1) NOT NULL,
	[lm_mID] [int] NULL,
	[lm_OnlineID] [int] NULL,
	[lm_ParsedName] [nvarchar](max) NOT NULL,
	[lm_Title] [nvarchar](300) NULL,
	[lm_Year] [int] NULL CONSTRAINT [DF_Local_Movies_lm_Year]  DEFAULT ((0)),
	[lm_Disk] [int] NULL CONSTRAINT [DF_Local_Movies_lm_Disk]  DEFAULT ((0)),
	[lm_ScanIgnored] [bit] NULL CONSTRAINT [DF_Local_Movies_lm_ScanIgnored]  DEFAULT ((0)),
	[lm_DuplicateLocalName] [bit] NULL,
	[lm_Hidden] [bit] NULL,
	[lm_LastUpdated] [datetime] NULL,
 CONSTRAINT [PK_Local_Movies] PRIMARY KEY CLUSTERED 
(
	[lm_ID] ASC
)  ON [PRIMARY]
) ON [PRIMARY]

Movies Table


CREATE TABLE [dbo].[Movies](
	[m_ID] [int] IDENTITY(1,1) NOT NULL,
	[m_OnlineID] [int] NULL,
	[m_Title] [nvarchar](400) NOT NULL,
	[m_LastUpdated] [datetime] NULL,
 CONSTRAINT [PK_M_ID] PRIMARY KEY CLUSTERED 
(
	[m_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

And here is some test data


INSERT INTO Local_Movies	(lm_ParsedName, lm_title, lm_Year, lm_Disk)
VALUES						('C:\Movies\300_CD1.avi', '300', '2006', 1)
INSERT INTO Local_Movies	(lm_ParsedName, lm_title, lm_Year, lm_Disk)
VALUES						('C:\Movies\300_CD2.avi', '300', '2006', 2)
INSERT INTO Local_Movies	(lm_ParsedName, lm_title, lm_Year, lm_Disk)
VALUES						('C:\Movies\The.Matrix_Part1.avi', 'The Matrix', '1999', 1)
INSERT INTO Local_Movies	(lm_ParsedName, lm_title, lm_Year, lm_Disk)
VALUES						('C:\Movies\The.Matrix_Part2.avi', 'The Matrix', '1999', 2)
INSERT INTO Local_Movies	(lm_ParsedName, lm_title, lm_Year, lm_Disk)
VALUES						('C:\Movies\16_Blocks.avi', '16 Blocks', '2006', 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources