0

trying update Sql table form a DataTable via a Stored procedure i wanted to avoid multiple round trips on every row insert as i already have a DataTable ready,

using Table-valued parameters i can efficiently pass a whole table to SQL Server.

problem is that i need to update another table with the passed table

this is the original Row By Row insert

ALTER PROC [dbo].[InsertNewFILES]
@FileId int, @DriveL nchar(1), @PathToFolder nvarchar(300), @ContainingFolder nvarchar (100), @CurFileName nvarchar(100), @fileExt nchar(10), @fileSize int, @created smalldatetime
AS BEGIN
insert into [FileLookUps] output inserted.FileID, inserted.FName, @DriveL, @Filepath, @FolderName, @FileExt, @FileSize, @created into [HddFolderFiles]
values(@CurFileName,'')

so in the row by row version above [HddFolderFiles] accepts all Row-columns and [FileLookUps] takes inserted.FileID+Fname Columns

as the idea was to pass a table but how can i achieve same with a whole table is passed... is this doable ?

Community
  • 1
  • 1
Raj Felix
  • 685
  • 5
  • 16
  • Yes it is doable. All you need to do is change from using a values list to a select statement. You will need to add your table valued parameter to this list of parameters of course. I assume your code here is only a portion as you have a ton of parameters not being used. – Sean Lange Nov 19 '15 at 22:11
  • @SeanLange i was pasting the initial "Test SPROC" my mistake, updated .. – Raj Felix Nov 19 '15 at 22:15
  • You won't be able to utilize a table parameter like this at all. Your current output is utilizing scalar values which is not going to be available if you switch to a table parameter. That is assuming that all those scalar values would be coming from your new table valued parameter. – Sean Lange Nov 19 '15 at 22:19
  • @SeanLange i was going to change into one table parameter so yes that list was going to be replaced by it in my plan, so offcourse no @ allthose parameters...as they don't exist.. – Raj Felix Nov 19 '15 at 22:23
  • Then unless those values are inserted you won't be able to get them in the OUTPUT because they don't exist in OUTPUT. It does seem though that you could easily do this with two insert statements. One to FileLoopUps and another into HddFolderFiles using your table parameter. It doesn't look like you need to use OUTPUT here really unless I am missing something. – Sean Lange Nov 20 '15 at 14:46

0 Answers0