SELECT TOP 10 *
INTO #TempValuesTable
FROM PEOPLE 


DECLARE 
	@object_id int,
	@InsertStr varchar(max) = '',
	@FieldsStr varchar(max) = '',
	@SQL varchar(max)

DECLARE @FieldTypes TABLE (FieldName sysname, FieldType int)
DECLARE @RESULT TABLE(FieldRow varchar(max))

SELECT TOP 1 
	@object_id = object_id
FROM tempdb.sys.tables
ORDER BY name DESC

INSERT @FieldTypes
SELECT 
	c.name,
	c.system_type_id
FROM tempdb.sys.columns c
JOIN tempdb.sys.types t ON t.system_type_id = c.system_type_id
WHERE
	object_id = @object_id

SELECT 
	@FieldsStr = @FieldsStr+IIF(@FieldsStr='','',',')+c.name,
	@InsertStr = @InsertStr+IIF(@InsertStr='','','+'',''+')+
	--приведение типов
	'CAST(ISNULL('+c.name+','''') AS varchar(255))'
	--приведение типов
FROM tempdb.sys.columns c
JOIN tempdb.sys.types t ON t.system_type_id = c.system_type_id
WHERE
	object_id = @object_id

SET @SQL = 'SELECT '+@InsertStr+' FROM #TempValuesTable'
INSERT @RESULT
(FieldRow)
EXEC (@SQL)

SET @SQL = ''

SELECT 
	@SQL = @SQL+'INSERT INTO SourceTable ('+@FieldsStr+')'+CHAR(13)+'VALUES('+FieldRow+')'+CHAR(13)+CHAR(13)
FROM @RESULT

SELECT @SQL

DROP TABLE #TempValuesTable

create table dbo.test (
    id int
  , xxx  varchar(20)
)
insert into dbo.test select 1, 'AAAA'

declare @header nvarchar(256) ='insert into NewTable('
select @header=@header +column_name +',' 
from INFORMATION_SCHEMA.COLUMNS
where table_name ='test'
order by ordinal_position

set @header  = LEFT(@header, LEN(@header) -1) +') values ('

select @header + CAST(ISNULL(id,0) as nvarchar(10)) +',''' +CAST(ISNULL(xxx, '') as nvarchar(50))+ ''')' from dbo.test

drop table dbo.test