bicky1980 (1/2/2013)

---

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

DECLARE @sql NVARCHAR(MAX),
    @totalrecs INT;

SET @sql = 'SELECT  @totalrecs AS [@totalrecs];';
PRINT @sql;
EXEC(@sql);

SELECT @totalrecs AS [@totalrecs];
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@totalrecs".

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

insert into destination
select top (expr) * from source
where age<50'

select @totalrecs=2000-count(*) from destination

2. What do you expect to happen when more than 2000 records are INSERTed into destination in the first query? In other words, 2000-count(*) is negative.

Roland Alexander STL (1/2/2013)

---

Well, it's not printing anything because @totalrecs is NULL when you concatentate it, and NULL concatenated with anything else yields NULL. You will need to assign @totalrecs a value. Why don't you just move all this into a stored procedure rather than trying to build a string and then execute it?

declare @totalrecs int

select @totalrecs=999
set @totalrecs=2000-@totalrecs
print @totalrecs