Create SQL INSERT STATEMENT WITHOUT KNOWING THE TABLE NAME... in a tirgger

I have a process I have inherited that requires me to create an insert statement, but the kicker is that I will not know the temp table's name ahead of time until it has been created. Now my code works as is, but it complains about the syntax and I was hoping there might be a better way of doing this. The code I would like to change is in orange.

Anyway, any advice would be greatly appreciated...

Here is the basic trigger:

Code Snippet

ALTER TRIGGER [dbo].[trg_DownloadDataTypes_ins] ON [dbo].[DownloadDataTypes]

INSTEAD OF INSERT

AS

SET NOCOUNT OFF

DECLARE @.TableName varchar(100),

@.FileType varchar(100),

@.FileName varchar(100),

@.CampusID varchar(15),

@.DateCreated DateTime,

@.ParentID int

IF (SELECT COUNT(FileName) FROM INSERTED) = 1

BEGIN

SELECT @.TableName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileType = (SELECT FileType FROM INSERTED)

SELECT @.CampusID = (SELECT CampusID FROM INSERTED)

SELECT @.DateCreated = (SELECT DateDownLoaded FROM INSERTED)

IF @.TableName = 'SyncTest-Deleteme'

BEGIN

DELETE FROM DownloadDataTypes WHERE [FileName] = 'SyncTest-DeleteMe'

END

ELSE

BEGIN

/*Inserting an IF/ELSE statement here dependant on @.FileType to seperate the handling of 'CAMPUS INVENTORY' file types.

IF @.FileType = 'CAMPUS INVENTORY'

BEGIN

DECLARE @.Qry varchar(4000)

--First, insert the record into tblSyncedInventory.

INSERT INTO tblSyncedInventory([FileName], FileType, CampusID, DateCreated)

SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

--Now, go out and get the child records from their temp table, insert them into tblSyncedInventoryDetails, then drop the temp table.

SET @.Qry = 'INSERT INTO tblSyncedInventoryDetails (FileID, ISBN, Copies, Accession, DateCreated, FileName) SELECT ' + str(@.ParentID) + ', dbo.[' + @.TableName + '].ISBN, dbo.[' + @.TableName + '].Copies, NULL, GETDATE(), dbo.[' + @.TableName + '].FileName FROM dbo.[' + @.TableName + ']'

EXEC (@.Qry)

--Now, drop the temp table.

SET @.Qry = 'DROP TABLE [' + @.TableName + ']'

EXEC (@.Qry)

END

ELSE

BEGIN

INSERT INTO dbo.tblSyncedInventory([FileName],[FileType],[CampusID],[DateCreated]) VALUES (@.TableName, @.FileType, @.CampusID, @.DateCreated)

INSERT INTO tblDownloadDataTypes ([FileName], FileType, CampusID, DateCreated) SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

EXEC stpro_ProcessPDAFileFromTrigger @.TableName, @.FileType, @.CampusID, @.ParentID

END

What error(s) are you receiving?

|||

I think you're missing one or more END statements. Each BEGIN should have a matching one

ALso, you can grab all the variables from INSERTED In one query:

SELECT @.TableName =[FileName],

@.FileName = [FileName],

@.FileType = FileType,

@.CampusID = CampusID,

@.DateCreated = DateDownLoaded

FROM INSERTED

Not sure about how temporary these temp tables are either, but without knowing the app or the rest of the code its difficult to suggest anything constructive.

HTH

sql