|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sending results of query into a tableA colleague is going to automate (using Reporting Services) a daily report
using the following sql. He asked that I put the results of this query into a table. How do I do this? Thanks. -hazz declare @now datetime set @now = '2006-01-20' select count(Create_DT) from customer where varDT > @now and varInt = 1 select count(Create_DT) from customer where varDT > @now and varInt = 2 select count(Create_DT) from customer where var DT > @now and varInt = 3 select count(Create_DT) from customer where var DT > @now and varInt = 4 select count(Create_DT) from customer where var DT > @now and varInt = 5 select varInt, Create_DT_Count = COUNT( Create_DT )
into yourtable from customer where varDT > @now and varInt between 1 and 5 group by varInt If its a regular job, you should create the table first and then truncate table and insert.... create table yourresults ( varInt tinyint not null constraint pk_yourresults primary key clustered, Create_DT_Count int not null ) go truncate table yourresults insert yourresults ( varInt, Create_DT_Count ) select varInt, Create_DT_Count = COUNT( Create_DT ) from customer where varDT > @now and varInt between 1 and 5 group by varInt Tony. Show quote "hazz" <h***@sonic.net> wrote in message news:O67kGNeHGHA.2912@tk2msftngp13.phx.gbl... >A colleague is going to automate (using Reporting Services) a daily report >using the following sql. He asked that I put the results of this query into >a table. How do I do this? Thanks. -hazz > > declare @now datetime > set @now = '2006-01-20' > select count(Create_DT) from customer where varDT > @now and varInt = 1 > select count(Create_DT) from customer where varDT > @now and varInt = 2 > select count(Create_DT) from customer where var DT > @now and varInt = 3 > select count(Create_DT) from customer where var DT > @now and varInt = 4 > select count(Create_DT) from customer where var DT > @now and varInt = 5 > Thank you Tony. Perfect! My one time query turned into a repetitive task.
Thanks for helping my automate it! -hazz Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:e%23JKyUeHGHA.3120@TK2MSFTNGP10.phx.gbl... > select varInt, Create_DT_Count = COUNT( Create_DT ) > into yourtable > from customer > where varDT > @now > and varInt between 1 and 5 > group by varInt > > If its a regular job, you should create the table first and then truncate > table and insert.... > > create table yourresults ( > varInt tinyint not null constraint pk_yourresults primary key > clustered, > Create_DT_Count int not null > ) > go > > truncate table yourresults > > insert yourresults ( varInt, Create_DT_Count ) > select varInt, Create_DT_Count = COUNT( Create_DT ) > from customer > where varDT > @now > and varInt between 1 and 5 > group by varInt > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "hazz" <h***@sonic.net> wrote in message > news:O67kGNeHGHA.2912@tk2msftngp13.phx.gbl... >>A colleague is going to automate (using Reporting Services) a daily report >>using the following sql. He asked that I put the results of this query >>into a table. How do I do this? Thanks. -hazz >> >> declare @now datetime >> set @now = '2006-01-20' >> select count(Create_DT) from customer where varDT > @now and varInt = 1 >> select count(Create_DT) from customer where varDT > @now and varInt = 2 >> select count(Create_DT) from customer where var DT > @now and varInt = 3 >> select count(Create_DT) from customer where var DT > @now and varInt = 4 >> select count(Create_DT) from customer where var DT > @now and varInt = 5 >> > > There are many ways, how do you (both) like this one:
declare @now datetime set @now = '2006-01-20' select varInt ,count(Create_DT) from customer where varDT > @now group by varInt Or, if you for some reason dislike varInt values outside 1,2,3,4,5: declare @now datetime set @now = '2006-01-20' select varInt ,count(Create_DT) from customer where (varDT > @now) and (varInt in (1,2,3,4,5)) group by varInt ML --- http://milambda.blogspot.com/ Thunder and lightning! I forgot the INSERT part. Look at Tony's example. He's
more focused, I guess... ML --- http://milambda.blogspot.com/ Not sure about that ML, its 6pm here, Friday evening; beers beckoning :)
Tony. Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:145E900B-C50F-4C6A-8426-C481E4D4048C@microsoft.com... > Thunder and lightning! I forgot the INSERT part. Look at Tony's example. > He's > more focused, I guess... > > > ML > > --- > http://milambda.blogspot.com/ 7pm over here. Thinking of beer, salivating profusely. Two beers, or not two
beers... ML --- http://milambda.blogspot.com/ I love it. Thunder and lighting.... Thanks for showing be both ways
.. -hazz Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:145E900B-C50F-4C6A-8426-C481E4D4048C@microsoft.com... > Thunder and lightning! I forgot the INSERT part. Look at Tony's example. > He's > more focused, I guess... > > > ML > > --- > http://milambda.blogspot.com/ I got it. Thank you very much ML.
Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:145E900B-C50F-4C6A-8426-C481E4D4048C@microsoft.com... > Thunder and lightning! I forgot the INSERT part. Look at Tony's example. > He's > more focused, I guess... > > > ML > > --- > http://milambda.blogspot.com/ |
|||||||||||||||||||||||
Other interesting topics