Conditionally entering flat file data into relational Stucture 1

[oliveto]

Hi,

I'm working on importing some data into my database. The data is currently in a SQL Server Table. I'm new to working with triggers (or performing more complex sql commands for that matter) but am trying to use a trigger on a view(that is identical to the table being imported) to handle a bulk insert.

I was under the impression that the "inserted" table handles one record at a time. When I figured out that isn't the case, I felt stumped as to how to make importing decisions on each record.

I have a Research Project Table consisting of ID,ProjectNum,Title
Also, there is a ResearchProjectPeriod table consisting of ID,ProjectID,From,To,DirectCost,IndirectCost

I'd like to check for projectNum on the records to import and if it exists in my database then only create associated entry in ResearchProjectPeriod table. If there is no associated ProjectNum then I would like to create the project as well as create an associated ResearchProjectPeriod Record.

Thanks for your help.

Stephen

 

[TheBugSlayer]

OK. So you are importing data from one table to the other somehow (DTS, BCP, etc), right?

Which one is the target table, a third one or any of ResearchProject and ResearchProjectPeriod?

Let me assume you are inserting in a third table (it's more convenient, youcan delete it later on), then you will have a trigger on that table that would check the existence of each ProjectNum inserted in your DB (which table) and take the appropriate decision according to the case, right?

So you trigger will be something like:

CREATE TRIGGER ChkPrjNumber ON ThirdTable
FOR insert AS
declare @TmpPrjNumber int
SELECT @TmpPrjNumber = ProjectNumber FROM ResearchProject RP INNER JOIN inserted I ON RP.ProjectNumber = I.ProjectNumber
IF @TmpPrjNumber <= 0 INSERT INTO ResearchProject --Complete the INSERT statement
INSERT INTO ResearchProjectPeriod --Complete the INSERT statement.
--You always INSERT in ResearchProjectPeriod regardless of the situation
GO


Hope this helps. If it does, rate me please.