March 17, 2010 at 2:51 am
I've tables and rows as follow,
declare @timerH table
(idx smallint,troutehidx smallint,seq tinyint,dprtweekday char(7));
/*idx is a primary key*/
/*Combination of troutehidx, and seq is a unique*/
insert into @timerH values(2,1,1,'1011101');
insert into @timerH values(7,2,1,'1111101');
insert into @timerH values(8,2,2,'0000011');
/*dprtweekday is a weekday*/
/*if 1011101, means Sunday, Tuesday, Wednesday, Thursday, and Saturday*/
/*if 1111100, means Sunday, Monday, Tuesday, Wednesday, Thursday, and Friday*/
declare @timerD table
(idx smallint,ttimerHidx smallint,dprttime smalldatetime);
/*idx is a primary key*/
/*ttimerHidx is a FK, and refer to @timerH(idx)*/
insert into @timerD values(2,2,'19000101 10:30');
insert into @timerD values(9,7,'19000101 09:00');
insert into @timerD values(10,7,'19000101 14:30');
insert into @timerD values(11,8,'19000101 11:00');
insert into @timerD values(12,8,'19000101 16:30');
Now, i'm generate trip schedule From: 18 Mar 2010 To: 21 Apr 2010 using T-SQL as follow
declare @sDte smalldatetime
set @sDte='3/18/2010'
declare @eDte smalldatetime
set @eDte='4/21/2010'
select idx= row_number() over (order by t1.troutehidx, t1.seq, t1.dprtdte),
t1.timerHidx,t1.troutehidx,t1.seq,t1.dprtdte,
t2.dprttime
from
(selectidx as timerHidx,troutehidx, seq, dprtdte
from
(
select idx,troutehidx, seq, dprtweekday, Tally.N,
dprtdte = dateadd(day, Tally.N-1, @sDte)
from @timerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
wheresubstring(dprtweekday, datepart(weekday, dprtdte), 1)= '1')t1
inner join
(select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx)t2
on t1.timerHidx=t2.idx
order by t1.troutehidx, t1.seq, dprtdte
So, the RESULTS as follow,
idx | timerHidx | troutehidx | seq | dprtdte | dprttime
------------------------------------------------------------------------------------------
121 12010-03-18 00:00:001900-01-01 10:30:00
221 12010-03-20 00:00:001900-01-01 10:30:00
321 12010-03-21 00:00:001900-01-01 10:30:00
421 12010-03-23 00:00:001900-01-01 10:30:00
521 12010-03-24 00:00:001900-01-01 10:30:00
621 12010-03-25 00:00:001900-01-01 10:30:00
721 12010-03-27 00:00:001900-01-01 10:30:00
.....
.....
.....
Now, i've temporary tables as follow,
CREATE TABLE #tripH
(idx smallint identity(1,1),timerHidx smallint, troutehidx smallint,
seq tinyint,dprtdte smalldatetime)
CREATE TABLE #tripD
(idx smallint identity(1,1),ttripHidx smallint,dprttime smalldatetime)
/*ttripHidx is a FK and refer to #tripH(idx)*/
My question is,
1. How T-SQL look's like to insert RESULTS into #tripH, and #tripD?
March 17, 2010 at 3:16 am
Hi,
SQL Server can't insert into two tables at the same time.
The best solution is to let your query insert it's result into a temporary table and then use the temporary table to insert rows in the other two tables:
select ...
into #Result
from ...
insert into #tripH
select ...
from #Result
[where ...]
insert into #tripD
select ...
from #Result
[where ...]
/Markus
March 17, 2010 at 3:18 am
Hey I didn't quite understand your question. What are you trying to do here?
Copy and process some data from existing tables, based on some conditions and insert the result into other tables?
And please do not use any function in the 'WHERE' clause (you have used SUBSTRING). It will reduce the performance greatly if your table size is considerably big.
Check this:
http://www.sqlservercentral.com/articles/Performance+Tuning/61809/
https://sqlroadie.com/
March 17, 2010 at 4:29 am
DECLARE @a TABLE (A INT NOT NULL);
DECLARE @b-2 TABLE (B INT NOT NULL);
DECLARE @C TABLE (C INT NOT NULL);
INSERT @a (A) VALUES (1);
INSERT @a (A) VALUES (2);
INSERT @a (A) VALUES (3);
INSERT @a (A) VALUES (4);
INSERT @a (A) VALUES (5);
INSERT @b-2 (B)
OUTPUT inserted.B
INTO @C (C)
SELECT A
FROM @a
WHERE A BETWEEN 2 AND 4;
SELECT *
FROM @a;
SELECT *
FROM @b-2;
SELECT *
FROM @C;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 5:40 am
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1')t1
for this code Sql optimizer doesnt use index seek/clustered seek.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 5:50 am
Bhuvnesh (3/17/2010)
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1')t1for this code Sql optimizer doesnt use index seek/clustered seek.
The whole expression seems odd, since DATEPART returns an integer - not a string type.
WEEKDAY is non-deterministic anyway so there is no way to use a seek with it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 12:09 pm
So far, me can insert into #tripH as follow,
CREATE TABLE #tripH
(idx smallint identity(1,1),timerHidx smallint, troutehidx smallint,
seq tinyint,dprtdte smalldatetime);
insert into #tripH
SELECT timerHidx ,troutehidx , seq ,dprtdte
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY timerHidx , troutehidx , seq , dprtdte ORDER BY idx) AS RowNo,
idx ,timerHidx ,troutehidx , seq ,dprtdte
from
(select idx = row_number() over (order by t1.troutehidx, t1.seq, t1.dprtdte),
t1.timerHidx,t1.troutehidx,t1.seq,t1.dprtdte,t2.dprttime
from
(select idx as timerHidx,troutehidx, seq, dprtdte
from
(
select idx,troutehidx, seq, dprtweekday, Tally.N,
dprtdte = dateadd(day, Tally.N-1, @sDte)
from @timerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1')t1
inner join
(select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx)t2
on t1.timerHidx=t2.idx
/*order by t1.troutehidx, t1.seq, dprtdte*/)t1)t2 where RowNo=1;
CREATE TABLE #tripD
(idx smallint identity(1,1),ttripHidx smallint,dprttime smalldatetime)
/*ttripHidx is a FK and refer to #tripH(idx)*/
drop table #tripH,#tripD
But i'm looking for help to insert the dprttime into #tripD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply