January 2, 2013 at 10:09 am
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
January 2, 2013 at 10:40 am
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
January 2, 2013 at 1:15 pm
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
January 2, 2013 at 6:11 pm
bicky1980 (1/2/2013)
Hi AllI 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>49I 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-@totalrecsThen 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 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
January 3, 2013 at 4:04 am
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
January 3, 2013 at 4:20 am
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
January 3, 2013 at 8:09 pm
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