Geek asks and answers

Not sure how to take data from one table and move it to another

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 am trying to move this to another table (called destination_table) that has columns:

month (datetime) traffic_count (integer)

How to create a loop in SQL to create a single row for October in a new table with full doors and doors without explicitly entering any data (for example, a month)? A.

+3
source share
2 answers

You can use the cursor, but if I understand what you are trying to do, then this is 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
+5
source

You do not create a loop; you are using an INSERT / SELECT combination.

SQL Server 2008:

INSERT INTO Destination_Table(Month, Traffic_Count)
    SELECT DATEADD(day, 1 - DAY(traffic_date), traffic_date),
           SUM(door_one + door_two)
      FROM Practice_Table
     GROUP BY DATEADD(day, 1 - DAY(traffic_date), traffic_date)

SQL Server 2012:

INSERT INTO Destination_Table(Month, Traffic_Count)
    SELECT DATEFROMPARTS(YEAR(traffic_date), MONTH(traffic_date), 1),
           SUM(door_one + door_two)
      FROM Practice_Table
     GROUP BY DATEFROMPARTS(YEAR(traffic_date), MONTH(traffic_date), 1)

2006 2006-10-01 Destination_Table.

DATE, DATETIME, . , DATETIMEFROMPARTS(), , .

NULL "", . , , , SUM(door_one) + SUM(door_two). , SUM, NULL ( , ), door_one + door_two NULL, NULL, . . , NOT NULL , , .

+3

More articles:


All Articles