Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Records in Table and.... Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:42 AM
Points: 215, Visits: 422
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
Post #1401946
Posted Wednesday, January 2, 2013 10:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 429, Visits: 332
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
Post #1401963
Posted Wednesday, January 2, 2013 1:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1402058
Posted Wednesday, January 2, 2013 6:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
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&gt;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!
Post #1402132
Posted Thursday, January 3, 2013 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:42 AM
Points: 215, Visits: 422

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
Post #1402279
Posted Thursday, January 3, 2013 4:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:42 AM
Points: 215, Visits: 422
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

Post #1402287
Posted Thursday, January 3, 2013 8:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:26 AM
Points: 14, Visits: 52
Maybe you can Try this

INSERT INTO DESTINATIONTABLE(TOTALCOUNT)
SELECT COUNT(FIELD) FROM SOURCETABLE
WHERE AGE = 49
Post #1402641
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse