Count Records in Table and....

  • 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

  • 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?

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • 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

    One other item of note, @totalrecs is not going to be available as a variable inside your dynamic SQL. A batch executed using EXEC (or sys.sp_executesql) is in a different scope when it comes to variables than the scope from which it is called. You can see what I mean by running this:

    DECLARE @sql NVARCHAR(MAX),

    @totalrecs INT;

    SET @sql = 'SELECT @totalrecs AS [@totalrecs];';

    PRINT @sql;

    EXEC(@sql);

    You'll receive this output:

    SELECT @totalrecs AS [@totalrecs];

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@totalrecs".

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • 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

    Here's my two cents worth:

    1. As an earlier responder pointed out, this doesn't need to be dynamic SQL because you can run:

    insert into destination

    select top (expr) * from source

    where age<50'

    Where expr is a query like:

    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.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • 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.

    Sorry- The first command should have said select top 2000

  • 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?

    Surely @totalrecs would be the figure which is remaining??

    declare @totalrecs int

    select @totalrecs=999

    set @totalrecs=2000-@totalrecs

    print @totalrecs

  • Maybe you can Try this

    INSERT INTO DESTINATIONTABLE(TOTALCOUNT)

    SELECT COUNT(FIELD) FROM SOURCETABLE

    WHERE AGE = 49

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply