0

I'm pulling values from RESTORE FILELISTONLY FROM DISK for multiple backup files into a temp table. I am trying to insert a set value (@dbSet) to a column (@dbSetC) but when running my insert it's not allowing me.

I'm not sure how to run the INSERT command and have both the result of the EXEC in the first columns then the @dbSet value into the @dbSetC column.

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
1

A possible solution would be to create a #tmpHold table with the same columns as #tmp minus the dbSet column. Insert the results of the exec into #tmpHold and then insert into #tmp the data from #tmpHold plus the extra column @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
  • PERFECT!!! TY so much! – Sam Wagner Oct 18 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.