August 14, 2009 at 11:05 am
I have a list of tables from one database being migrated into another. To validate the data, I'm writing a query to compare the totals and report if they're different. I can write out the table names from the source table, however in case the final source database is different, I'd like to avoid that. So I'm grabbing the list of tables and using an executesql to do the count query, where the statement has the tablename from a variable.
My next step is to take that total returned, and subtract my processed total from it, but I don't know how to do that. How do I get the result of the executesql (which is returning a count(record) result) into a variable to perform the next step of "@sourcetable - @destinationtable" where I want @sourcetable to be the count my executesql returned?
Melinda
August 14, 2009 at 11:19 am
You can catch the output in a table variable, and then select it into your variable from the table variable.
Inelegant, but effective.
declare @sql nchar(4000)
declare @catchtable table (xTotal int)
declare @result int
-- create a sample table and populate with a wee bit of data
create table #temp (xValue int)
insert into #temp
select 1 union all
select 2 union all
select 3 union all
select 25
-- build query string
set @sql = 'select sum(xvalue) as xtotal from #temp'
-- execute query string and catch total in table variable
insert into @catchTable
exec sp_executesql @sql
-- assign total to @result variable
select @result = xtotal from @catchtable
select @result as [result]
drop table #temp
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply