making a text from a select statement to execute

just to test, i tried

exec 'select '''select count(*) from sys.tables''''

but that won't work

can you provide some input and output required??

Something like that (in general) will show you how to execute sql dynamically.

declare
	@TxtSql nvarchar(max)
	
select @TxtSql= query_text_column from dbo.MyTable where CommandId = 1000

sp_executesql @TxtSql

Select allOpen in new window

Never need to say that this is dangerous thing from the multiple standpoints. First, it's security risk - think about sql injection and all other interesting issues. Second, dynamic sql introduces recompilations, blows up plan cache, etc.

thanks for the warning.. what if it is used only once in a while, and not public.. in the context of the below..

right now, it gives select statements which are copied and executed separately.. the users want it all done at once..

use RASP2
go
create function dbo.getColumnNames(@tablecatalog sysname, @tableschema sysname, @tablename sysname)
returns varchar(8000) -- or max for 2005+
as
begin
declare @vc varchar(8000) -- or max for 2005+
select @vc = coalesce(@vc + ',', '') + column_name
from information_schema.columns
where table_name = @tablename
	and table_schema=@tableschema
	and table_catalog=@tablecatalog
order by ordinal_position
return @vc
end
go
select 'insert into '+ table_catalog+'.'+table_schema+'.'+table_name +
' select * from RASP3.'+table_schema+'.'+table_name
 from information_schema.tables where table_catalog+'.'+table_schema+'.'+table_name
not in (SELECT table_catalog+'.'+table_schema+'.'+table_name TableName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMNPROPERTY(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1)

(SELECT 'set identity_insert '+table_catalog+'.'+table_schema+'.'+table_name + ' on '+
 'insert into '+ table_catalog+'.'+table_schema+'.'+
table_name +'( ' + dbo.getColumnNames(table_catalog, table_schema, table_name) + 
') select * from RASP3.'+table_schema+'.'+table_name+
' set identity_insert '+table_catalog+'.'+table_schema+'.'+table_name + ' off'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMNPROPERTY(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1)


drop function dbo.getColumnNames

Select allOpen in new window

dwkor, you said "Your code would not work on SQL 2000 because of Information_Schema view"
ist that part of sql 2000 also? http://msdn.microsoft.com/en-us/library/aa933204%28v=sql.80%29.aspx

in the new code do you do the" set identity_insert" for both tables with and without identity  columns? is that no problem?

also, is it possible to put the individual statements on separate lines?