sql server - Generate insert statement with the data from another table dynamically -

i create stored procedure , generate insert statement table dynamically. input parameters stored procedure supposed schema, table name, @col1, @col2, ..., @coln. stored procedure supposed take 1 random record server , based on record supposed generate insert statement. @col1, @col2, ..., @coln parameters optional in case overwrite original value 1 need.

the insert record supposed that:

insert schema_name.table_name values ( col1, col2, ..., coln) values ( coalesce(@col1, 'col1_value'), coalesce(@col2, 'col2_value'), ..., coalesce(@coln, 'coln_value') ); 

currently can not realize how take real data , put statement. did is:

create procedure dbo.generatesampledatainsertsp     @schemaname varchar(255),     @tablename varchar(255) set nocount on; declare @sql varchar(max) = '',     @columns varchar(max) = '',     @columnswithcoalesce varchar(max) = '';  select  c.name    #column    sys.tables t         join sys.schemas s on s.schema_id = t.schema_id         join sys.columns c on c.object_id = t.object_id         join sys.types tt on c.system_type_id = tt.system_type_id   t.name = @tablename         , s.name = @schemaname         , tt.name not in ( 'timestamp' );   set @columns = null;  select  @columns = isnull(@columns + ', ', '') + name    #column;  set @sql = 'select top 1 ' + @columns + ' anotherdatabase.' + @schemaname + '.' + @tablename + ' order newid();';    set @sql = 'insert [' + @schemaname + '].[' + @tablename + '] (' + @columns + ') values ();';  select  @sql; 

i not care ideal code or solution. need result , that's it.

updated:

-- example #1

use tempdb go  /*create procedure dbo.generatesampledatainsertsp ...*/  create table dbo.employee (id int, employeename varchar(255)); insert dbo.employee values (1, 'john smith');  exec dbo.generatesampledatainsertsp @schemaname = 'dbo', @tablename = 'employees';  ------------------------ expected output of procedure (not action, plain text) ------------------  insert dbo.employee (  id,  employeename ) values (  coalesce(@id, '1'),   coalesce(@employeename, 'john smith')  ); 

-- example #2

use tempdb go  /*create procedure dbo.generatesampledatainsertsp ...*/  create table dbo.orders (id int, ordernbr varchar(10), orderdate date, customerid id);  insert dbo.orders values (7, '12345678', getdate(), 1024);  exec dbo.generatesampledatainsertsp @schemaname = 'dbo', @tablename = 'orders';  ------------------------ expected output of procedure (not action, plain text) ------------------  insert dbo.orders  (   id,   ordernbr,   orderdate,   customerid ) values (   coalesce(@id, '7'),   coalesce(@ordernbr,'12345678'),   coalesce(@orderdate, '2015-07-05'),   coalesce(@customerid, '1024') ); 

ok, i'll answer own question. said not care code beauty , performance, need result provide more elegant solution accepted solved solution. here code:

create procedure dbo.generatesampledatainsertsp     @schemaname varchar(255),     @tablename varchar(255) set nocount on;  if exists ( select  name                tempdb.sys.tables               name '%##record%' ) begin     drop table ##record; end; declare @sql varchar(max) = '',     @columns varchar(max) = '',     @columnswithcoalesce varchar(max) = '';  select  c.name    #column    sys.tables t         join sys.schemas s on s.schema_id = t.schema_id         join sys.columns c on c.object_id = t.object_id         join sys.types tt on c.system_type_id = tt.system_type_id   t.name = @tablename         , s.name = @schemaname         , tt.name not in ( 'timestamp' );   set @columns = null;  select  @columns = isnull(@columns + ', ', '') + name    #column;  set @sql = 'select top 1 ' + @columns + ' ##record anotherdatabase.' + @schemaname + '.' + @tablename + ' order newid();';  exec (@sql);  set @sql = 'insert [' + @schemaname + '].[' + @tablename + '] (' + @columns + ') values (';  declare @columnscur cursor, @columnname varchar(255), @tmpvalue varchar(max), @sqlcommand nvarchar(1000); set @columnscur = cursor select name #column; open @columnscur; fetch next @columnscur @columnname; while @@fetch_status = 0 begin     set @sqlcommand = 'select @value=cast(' + @columnname + ' varchar(max)) ##record;'     execute sp_executesql @sqlcommand, n'@value varchar(max) output', @value=@tmpvalue output     set @sql = @sql + 'coalesce(@'+ @columnname +', ''' + @tmpvalue + '''),';     fetch next @columnscur @columnname; end; close @columnscur; deallocate @columnscur;   set @sql = @sql + ');'  set @sql = replace(@sql, ',);', ');');  select  @sql;  go