insertin data into new table
-
hi. could someone please help me out. im tryin to take data from one table, summarize it, and insert it into a new table. what i have is a current 'daily' stats table for a user. but now what i want to do is take that data and add up 7 days worth of stats for each user. so, in other words, i have the last weeks stats for each user. basically i need to know how to select data out daily table and then insert into weekly table. Thanks!
-
hi. could someone please help me out. im tryin to take data from one table, summarize it, and insert it into a new table. what i have is a current 'daily' stats table for a user. but now what i want to do is take that data and add up 7 days worth of stats for each user. so, in other words, i have the last weeks stats for each user. basically i need to know how to select data out daily table and then insert into weekly table. Thanks!
The INSERT command can contain a SELECT INSERT INTO Destination SELECT Column1, Column2, Column3 FROM SourceTable
Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
hi. could someone please help me out. im tryin to take data from one table, summarize it, and insert it into a new table. what i have is a current 'daily' stats table for a user. but now what i want to do is take that data and add up 7 days worth of stats for each user. so, in other words, i have the last weeks stats for each user. basically i need to know how to select data out daily table and then insert into weekly table. Thanks!
Depending on what you mean by "summarize it", I suspect you may be looking for a "GROUP BY" construct. INSERT INTO tblWeekly SELECT userName, SUM(stats) FROM tblDaily GROUP BY userName; Can you give a few-record example of 'daily stats' and then what a summary of those stats should look like in the weekly table? David
-
Depending on what you mean by "summarize it", I suspect you may be looking for a "GROUP BY" construct. INSERT INTO tblWeekly SELECT userName, SUM(stats) FROM tblDaily GROUP BY userName; Can you give a few-record example of 'daily stats' and then what a summary of those stats should look like in the weekly table? David
thanks for the reply. i have a table "DailyUsage", which has 3 fields, namely Username, TheDate, DailyCount. primary key is a combination of Username and TheDate. Now, because this table is pretty big, cos its storing daily usage for the last year, I want to do a sort of round robin methodoligy so my reports can run alot quicker. So, ive created a table called "weeklyUsage" which will have Username, TheDate, and WeeklyCount. tbl WeeklyUsage will have only for example the last 3 or 4 weeks of data in it. So, I need to do a SUM of DailyCounts for each username for the last 3 or 4 weeks and then insert into tbl WeeklyUsage...
-
thanks for the reply. i have a table "DailyUsage", which has 3 fields, namely Username, TheDate, DailyCount. primary key is a combination of Username and TheDate. Now, because this table is pretty big, cos its storing daily usage for the last year, I want to do a sort of round robin methodoligy so my reports can run alot quicker. So, ive created a table called "weeklyUsage" which will have Username, TheDate, and WeeklyCount. tbl WeeklyUsage will have only for example the last 3 or 4 weeks of data in it. So, I need to do a SUM of DailyCounts for each username for the last 3 or 4 weeks and then insert into tbl WeeklyUsage...
-
Sorry about taking so long to respond; I have been out of town. How about INSERT INTO weeklyUsage SELECT Username, SUM(DailyCount) FROM DailyUsage GROUP BY Username WHERE TheDate > DATE() - 21; to get summaries for the last three weeks. David
-
thanks for the reply. i tried that DATE() function but its not working, im using SQL Server.dont know if thats the problem. Is that 'DATE()' suppose to give me the current date?
Yes, the current date. In Oracle, it's "SYSDATE()" plus some other synonyms. I don't use SQL Server, and don't know what it wants. Since you didn't give examples, I am not sure the query I provided will give what you want, but from your description, i *THINK* it's what you want. Let me know how it turns out. David
-
Yes, the current date. In Oracle, it's "SYSDATE()" plus some other synonyms. I don't use SQL Server, and don't know what it wants. Since you didn't give examples, I am not sure the query I provided will give what you want, but from your description, i *THINK* it's what you want. Let me know how it turns out. David