Unsure how to take data from one table and move it into another

sql, sql-server-2008, t-sql

Solution

You could use a cursor, but if I understand what your trying to do, then it's not necessary.

;WITH tmp AS (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, traffic_date), 0) AS month_field, door_one, door_two
    FROM practice_table
)
INSERT INTO destination_table (month, traffic_count)
SELECT month_field, SUM(door_one + door_two)
FROM tmp
GROUP BY month_field

Problem

I have a table: ``` create table practice_table ( traffic_date datetime , door_one integer , door_two integer ) ``` With some sample data: ``` insert into practice_table(traffic_date, door_one, door_two) values ('12-Oct-2006' ,14500 ,11141) insert into practice_table(traffic_date, door_one, door_two) values ('13-Oct-2006' ,6804 ,5263) insert into practice_table(traffic_date, door_one, door_two) values ('14-Oct-2006' ,7550 ,6773) insert into practice_table(traffic_date, door_one, door_two) values ('15-Oct-2006' ,6144 ,5211) insert into practice_table(traffic_date, door_one, door_two) values ('16-Oct-2006' ,5680 ,3977) insert into practice_table(traffic_date, door_one, door_two) values ('17-Oct-2006' ,5199 ,3918) insert into practice_table(traffic_date, door_one, door_two) values ('18-Oct-2006' ,5298 ,3631) ``` I'm trying to move this into another table (called destination_table) that has the columns: month (datetime) traffic_count (integer) How do I create a loop in SQL to create one row for October in the new table with the total of door_one and door_two without explicitly typing any data in (such as the month)?

Original source