I've problems.

I've 3 problems. First, I've data like this:
Date1 Date2
--
1/1/2005 1/7/2005 {m/d/yyyy}
1/8/2005 1/14/2005
1/15/2005 1/21/2005
If I input date 1/9/2005, so data at record 2 is showed. Because date
1/9/2005 in between 1/8/2005 and 1/14/2005.
If I input date 1/20/2005, so data at record 3 is showed. Because date
1/20/2005 in between 1/15/2005 and 1/21/2005.
and on...
Nah, How syntax SQL to select data above?
Second, I've 2 tables (A & B). I wanna import from table A to B. if Data in
table A exist at table B, so data at table B will be updated. and if Data in
table A not exist at table B, so data at table B will be inserted.
How syntax SQL to do it? Can it only one statement?
Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
its field?
INSERT INTO A (a,b,c,........)
SELECT a,b,c,..........
from B
Or any there other ways to write Syntax SQL (INSERT INTO) so short?> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
DECLARE @.dt SMALLDATETIME
SET @.dt = '20050120'
SELECT Date1, Date2 FROM tablename
WHERE Date1 <= @.dt AND Date2 >= @.dt
Always use YYYYMMDD format when inputting dates. And keep in mind that your
date values are *NOT* stored as the m/d/yyyy format you indicated, unless
they are not DATETIME/SMALLDATETIME.

> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
> in
> table A exist at table B, so data at table B will be updated. and if Data
> in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
No, you will need to run a separate INSERT and UPDATE. Or DELETE the rows
from table B that exist in table A, then insert the whole lot.

> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
YES. It is easy to get this list, open Query Analyzer, hit F8, find the
table, expand it, and click / drag the Columns folder to the query window.

> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
Why do we need a shortcut? If you really like using SELECT *, what do you
do when someone changes the structure of the table?|||Q1:
declare @.d datetime
set @.d = '20050115'
select * from t1 where date1 <= @.d and date2 >= @.d
go
Q2:
You need two statements.
update tb
set tb.c1 = (select c1 from ta where ta.pk = tb.pk), ..., tb.cn = (select cn
from ta where ta.pk = tb.pk)
where exists(select * from ta where ta.pk = tb.pk)
insert into tb
select c1, ..., cn
from ta
where not exists(select * from tb as b where b.pk = ta.pk)
Q3:
Yes
AMB
"Bpk. Adi Wira Kusuma" wrote:

> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
in
> table A exist at table B, so data at table B will be updated. and if Data
in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
> INSERT INTO A (a,b,c,........)
> SELECT a,b,c,..........
> from B
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
>
>|||Thanks for your answerings. But I dont undrstand with your statement. You
said that Always use YYYYMMDD format to filter date. Because I always use
mm/dd/yyyy format to filter date. and it can do properly. Example:
SELECT * FROM TB Where BornDate='1/28/2000'
Why can it do? please explain me so detail.
Second, I ask to you. How to delete data at table A that exist at table B.
Usually I write like this:
DELETE FROM TA where NOID in (SELECT NOID FROM TB).
But it can works, if at table A (TA) has 1 field to be primary key. If table
A (TA) has 4 fields to be primary key. How its syntax so good?|||> Thanks for your answerings. But I dont undrstand with your statement. You
> said that Always use YYYYMMDD format to filter date. Because I always use
> mm/dd/yyyy format to filter date. and it can do properly.
Please read both of these in their entirety:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp