bicky1980 (1/2/2013)

---

Hi All

I am just after a bit of clarification. I am writing a procedure which inserts data from one table (Source) into another (Destination) using certain criteria. (E.g. People Aged 50+)

insert into destination select * from source where age>49

I then want to set a variable using the numbers of records I have just appended into this table (Destination) - select @totalrecs=count(*) from destination

The Next step is to peform a calculation (2000-[no of records inserted into destination table])

select @totalrecs=2000-@totalrecs

Then I want to insert into the destination, using the result of the calculation,

insert into destination select top @totalrecs from source where [criteria] order by newid()

Now I am trying to do all this in one fluent procedure - could someone advise me of the best way to do this?

I am currently attempting to do this in the following manner:

declare @sql nvarchar(1000),

@totalrecs int

set @sql='insert into source select * from destination where age>49

select @totalrecs=count(*) from destination

set @totalrecs=2000-@totalrecs

insert into destination

select top '+CONVERT(NVARCHAR(10),@TOTALRECS)+' * from source

where age<50'

Print @sql

Exec(@sql)

Nothing is returned to the screen (from the print command) and the query is not executed - Anybody have an idea why and maybe suggest a better way to perform this

Thanks