SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count Records in Table and....


Count Records in Table and....

Author
Message
bicky1980
bicky1980
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 507
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
Roland Alexander STL
Roland Alexander STL
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 489
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 Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38486 Visits: 14411
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
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17359 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
bicky1980
bicky1980
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 507

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
bicky1980
bicky1980
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 507
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


math martinez
math martinez
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 62
Maybe you can Try this

INSERT INTO DESTINATIONTABLE(TOTALCOUNT)
SELECT COUNT(FIELD) FROM SOURCETABLE
WHERE AGE = 49
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search