I have 2 tables. One gets a nightly feed of data. The second table contains my own calculations based off of the data from the feed.
Here is the sql that I'm using to make the calculations and make my customized data. It's very slow. Any thoughts on how to speed it up would be GREATLY appreciated. I imagine there's probably a great way to perform this operation without a cursor, but I'm not sure how to do it.
Basically, the data in table_a has a date based on a seconds. I'm trying to convert the seconds into a date and time value, then insert into my customized table. Since I'm doing this nightly, I only want to do the inserts of data that I have not previously inserted into the table.
declare @create_date Datetime
declare @the_id varchar(200)
DECLARE my_cursor CURSOR FOR
select table_a.the_id as [the_id], CONVERT(datetime, DATEADD(s, dateseconds, '1970-01-01 00:00:00.000') , 121) as [create_datetime]
from table_a left outer join table_a_custom
on table_a.the_id = table_a_custom.the_id
where table_a_custom.the_id is null
OPEN my_cursor
FETCH FROM my_cursor INTO @the_id, @create_date
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table_a_custom(the_id, the_create_date) values (@the_id, @create_date)
FETCH FROM my_cursor INTO @the_id, @create_date
END
CLOSE my_cursor
DEALLOCATE my_cursor
Here is the sql that I'm using to make the calculations and make my customized data. It's very slow. Any thoughts on how to speed it up would be GREATLY appreciated. I imagine there's probably a great way to perform this operation without a cursor, but I'm not sure how to do it.
Basically, the data in table_a has a date based on a seconds. I'm trying to convert the seconds into a date and time value, then insert into my customized table. Since I'm doing this nightly, I only want to do the inserts of data that I have not previously inserted into the table.
declare @create_date Datetime
declare @the_id varchar(200)
DECLARE my_cursor CURSOR FOR
select table_a.the_id as [the_id], CONVERT(datetime, DATEADD(s, dateseconds, '1970-01-01 00:00:00.000') , 121) as [create_datetime]
from table_a left outer join table_a_custom
on table_a.the_id = table_a_custom.the_id
where table_a_custom.the_id is null
OPEN my_cursor
FETCH FROM my_cursor INTO @the_id, @create_date
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table_a_custom(the_id, the_create_date) values (@the_id, @create_date)
FETCH FROM my_cursor INTO @the_id, @create_date
END
CLOSE my_cursor
DEALLOCATE my_cursor
