參考來源：https://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script-sql-server
CREATE PROCEDURE dbo.ConvertQueryToInsert (@input NVARCHAR(max), @target NVARCHAR(max)) AS BEGIN
DECLARE @fields NVARCHAR(max);
DECLARE @select NVARCHAR(max);
-- Get the defintion from sys.columns and assemble a string with the fields/transformations for the dynamic query
SELECT
@fields = COALESCE(@fields + ', ', '') + '[' + name +']',
@select = COALESCE(@select + ', ', '') + ''''''' + ISNULL(CAST([' + name + '] AS NVARCHAR(max)), ''NULL'')+'''''''
FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID(N'tempdb..'+@input);
-- Run the a dynamic query with the fields from @select into a new temp table
CREATE TABLE #ConvertQueryToInsertTemp (strings nvarchar(max))
DECLARE @stmt NVARCHAR(max) = 'INSERT INTO #ConvertQueryToInsertTemp SELECT '''+ @select + ''' AS [strings] FROM '+@input
exec sp_executesql @stmt
-- Output the final insert statement
SELECT 'INSERT INTO ' + @target + ' (' + @fields + ') VALUES (' + REPLACE(strings, '''NULL''', 'NULL') +')' FROM #ConvertQueryToInsertTemp
-- Clean up temp tables
DROP TABLE #ConvertQueryToInsertTemp
SET @stmt = 'DROP TABLE ' + @input
exec sp_executesql @stmt
END
-- Example table
-- Run query and procedure
SELECT * INTO #TempTableForConvert FROM TABLE_NAME WHERE CON_YEAR='2017';
EXEC dbo.ConvertQueryToInsert '#TempTableForConvert', 'apuser.TABLE_NAME '
