將設定值插入數據庫

我正在將多個備份文件的 RESTORE FILELISTONLY FROM DISK 值提取到臨時表中。我正在嘗試將設定值（@dbSet）插入列（@dbSetC），但是在執行我的插入時它不允許我。

我不確定如何執行 INSERT 命令並將 EXEC 的結果放在第一列中，然後將 @dbSet 值放入 @dbSetC 列中。

create table #tmpA
(
   idx int identity(1,1),
   lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
   Select 
   'C:\temp\db.bak' union select
   'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
   LogicalName nvarchar(128)
   ,PhysicalName nvarchar(260)
   ,Type char(1)
   ,FileGroupName nvarchar(128)
   ,Size numeric(20,0)
   ,MaxSize numeric(20,0)
   ,FileId tinyint
   ,CreateLSN numeric(25,0)
   ,DropLSN numeric(25, 0)
   ,UniqueID uniqueidentifier
   ,ReadOnlyLSN numeric(25,0)
   ,ReadWriteLSN numeric(25,0)
   ,BackupSizeInBytes bigint
   ,SourceBlockSize int
   ,FileGroupId int
   ,LogGroupGUID uniqueidentifier
   ,DifferentialBaseLSN numeric(25,0)
   ,DifferentialBaseGUID uniqueidentifier
   ,IsReadOnly bit
   ,IsPresent bit
   ,TDEThumbprint varbinary(32)
   ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)

declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
   SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
   SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

   INSERT INTO #tmp
       EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
       SET @countr = (@countr + 1)
END

一種可能的解決方案是創建一個#tmpHold表，其中的列與#tmp減去列的dbSet列相同。將 的結果插入exec到 #tmpHold 中，然後插入到#tmp來自#tmpHoldplus 額外列的數據中@dbSetc。

create table #tmpA
(
   idx int identity(1,1),
   lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
   Select 
   'C:\temp\db.bak' union select
   'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
   LogicalName nvarchar(128)
   ,PhysicalName nvarchar(260)
   ,Type char(1)
   ,FileGroupName nvarchar(128)
   ,Size numeric(20,0)
   ,MaxSize numeric(20,0)
   ,FileId tinyint
   ,CreateLSN numeric(25,0)
   ,DropLSN numeric(25, 0)
   ,UniqueID uniqueidentifier
   ,ReadOnlyLSN numeric(25,0)
   ,ReadWriteLSN numeric(25,0)
   ,BackupSizeInBytes bigint
   ,SourceBlockSize int
   ,FileGroupId int
   ,LogGroupGUID uniqueidentifier
   ,DifferentialBaseLSN numeric(25,0)
   ,DifferentialBaseGUID uniqueidentifier
   ,IsReadOnly bit
   ,IsPresent bit
   ,TDEThumbprint varbinary(32)
   ,SnapshotUrl nvarchar(128)
   ,dbSetC int
)

create table #tmpHold
(
   LogicalName nvarchar(128)
   ,PhysicalName nvarchar(260)
   ,Type char(1)
   ,FileGroupName nvarchar(128)
   ,Size numeric(20,0)
   ,MaxSize numeric(20,0)
   ,FileId tinyint
   ,CreateLSN numeric(25,0)
   ,DropLSN numeric(25, 0)
   ,UniqueID uniqueidentifier
   ,ReadOnlyLSN numeric(25,0)
   ,ReadWriteLSN numeric(25,0)
   ,BackupSizeInBytes bigint
   ,SourceBlockSize int
   ,FileGroupId int
   ,LogGroupGUID uniqueidentifier
   ,DifferentialBaseLSN numeric(25,0)
   ,DifferentialBaseGUID uniqueidentifier
   ,IsReadOnly bit
   ,IsPresent bit
   ,TDEThumbprint varbinary(32)
   ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)
declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
   SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
   SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

   TRUNCATE TABLE #tmpHold;
   INSERT INTO #tmpHold    --insert results of EXEC into #tmpHold
       EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
   INSERT INTO #tmp    --insert #tempHold + @dbSet into #tmp
       SELECT *,@dbSet FROM #tmpHold
   SET @countr = (@countr + 1)
END

引用自：https://dba.stackexchange.com/questions/251466