{"id":86,"date":"2014-10-16T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/dynamically-create-table-results-from-non-query"},"modified":"2026-03-18T21:51:19","modified_gmt":"2026-03-18T21:51:19","slug":"dynamically-create-table-results-from-non-query","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/10\/16\/dynamically-create-table-results-from-non-query\/","title":{"rendered":"Dynamically create table results from non-query"},"content":{"rendered":"

Have you ever executed a command with a result set but you didn\u2019t know the exact schema being returned? Have you ever wanted to create a temporary table from a query but don\u2019t want to look up all of the column data types? Of course you have! T-SQL\u2019s SELECT \u2026 INTO<\/a>\u00a0makes creating tables from a result set easy. But what about commands with result sets which are not SELECT statements? In that case, we are left with manually building these tables\u00a0ourselves.<\/p>\n

Maybe\u00a0you\u2019ve returned the file list from a backup file so that you could\u00a0dynamically create the necessary MOVE statements for your RESTORE command.<\/p>\n

DECLARE @FileList TABLE\n(\n      LogicalName nvarchar(128) NOT NULL,\n      PhysicalName nvarchar(260) NOT NULL,\n      Type char(1) NOT NULL,\n      FileGroupName nvarchar(120) NULL,\n      Size numeric(20, 0) NOT NULL,\n      MaxSize numeric(20, 0) NOT NULL,\n      FileID bigint NULL,\n      CreateLSN numeric(25,0) NULL,\n      DropLSN numeric(25,0) NULL,\n      UniqueID uniqueidentifier NULL,\n      ReadOnlyLSN numeric(25,0) NULL ,\n      ReadWriteLSN numeric(25,0) NULL,\n      BackupSizeInBytes bigint NULL,\n      SourceBlockSize int NULL,\n      FileGroupID int NULL,\n      LogGroupGUID uniqueidentifier NULL,\n      DifferentialBaseLSN numeric(25,0)NULL,\n      DifferentialBaseGUID uniqueidentifier NULL,\n      IsReadOnly bit NULL,\n      IsPresent bit NULL,\n      TDEThumbprint varbinary(32) NULL\n);\n\n
INSERT INTO @fileList\nRESTORE FILELISTONLY FROM DISK = 'C:\\foo\\backupfile.bak';<\/code><\/pre>\n<\/p>\n
Then you stepped it up notch and automated the restores by searching a directory.<\/p>\n
CREATE TABLE #FileInfo(\nID INT IDENTITY(1, 1),\nName VARCHAR(200),\nParentId INT,\nDepth INT,\nISFILE BIT)\n\n
DECLARE @Directory VARCHAR(8000) = 'C:\\foo'<\/p>\n\n
INSERT INTO #FileInfo(Name, Depth, ISFILE)\n EXEC xp_dirtree @Directory, 0, 1<\/code><\/pre>\n<\/p>\n
Man that was a lot of code. <\/strong>What happens if Microsoft decides to change the data types or results for those commands? Will you need to maintain multiple versions of your temporary tables with a server version check thrown in?<\/p>\n
Nah\u2026 this is one more place where PowerShell is awesome.<\/p>\n
Import-Module SQLPS -DisableNameChecking;\n\n
$table = Invoke-Sqlcmd -ServerInstance localhost\\sql2014_2 -Database BackupTest -Query \"RESTORE FILELISTONLY FROM DISK = 'C:\\Users\\Derik\\Desktop\\project\\sqlHammer\\DB Backups\\AdventureWorks2012_FULL_1.bak'\";<\/p>\n\n
$table | Format-Table -AutoSize<\/code><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
It is as simple as that. Import the SQLPS module, execute the query, and a table is returned without any need to define it in advance.<\/p>\n","protected":false},"excerpt":{"rendered":"
Have you ever executed a command with a result set but you didn\u2019t know the exact schema being returned? Have you ever wanted to create a temporary table from a query but don\u2019t want to look up all of the column data types? Of course you have! T-SQL\u2019s SELECT \u2026 INTO\u00a0makes creating tables from a […]<\/p>\n","protected":false},"author":1,"featured_media":529,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-86","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"predecessor-version":[{"id":349,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/86\/revisions\/349"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/529"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}