Batch execution

Hello All,
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:

> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com