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

ITW Errors

Hi,
I'm trying to use the ITW for one of our German servers. I have created an
input file using Profiler that captures the SQL:BatchCompleted and the
RPC:Completed events. When I run the ITW using the input file I recieve the
following error:
"The workload does not contain any events or queries that can be tuned
against the current database. Check to see that the trace contains valid
events or that the SQL script contains valid queries and you are tuning
against the right database"
If I select only certain tables to analyze I recieve the following error:
"There are no events in the workload. Either the trace file contained no
SQL batch or RPC events or the SQL script contained no SQL queries.
I have run a broad trace and a narrow trace using the specific database id
as a filter.
Both using a file and a table return the same errors.
If I use the Query Analyzer Selection option with the ITW with a specific
query from the input file then the ITW does work properly. Also, the size
of the input file is < 100 rows (just a test at this point).
It looks as though the ITW has an issue with the code of one or more of the
statements in the input file. Is this an accurate assumption? Does anyone
know how to resolve this issue so that I can use ITW with many statements as
opposed to using only a few at a time?
Thanks
JerryJerry Spivey wrote:
> Hi,
> I'm trying to use the ITW for one of our German servers. I have
> created an input file using Profiler that captures the
> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
> using the input file I recieve the following error:
> "The workload does not contain any events or queries that can be tuned
> against the current database. Check to see that the trace contains
> valid events or that the SQL script contains valid queries and you
> are tuning against the right database"
> If I select only certain tables to analyze I recieve the following
> error:
> "There are no events in the workload. Either the trace file
> contained no SQL batch or RPC events or the SQL script contained no
> SQL queries.
> I have run a broad trace and a narrow trace using the specific
> database id as a filter.
> Both using a file and a table return the same errors.
> If I use the Query Analyzer Selection option with the ITW with a
> specific query from the input file then the ITW does work properly.
> Also, the size of the input file is < 100 rows (just a test at this
> point).
> It looks as though the ITW has an issue with the code of one or more
> of the statements in the input file. Is this an accurate assumption?
> Does anyone know how to resolve this issue so that I can use ITW with
> many statements as opposed to using only a few at a time?
> Thanks
> Jerry
There used to be a problem with SQL Server 7.0 with running the ITW on a
client PC using a different locale than the one on the server. Have you
tried running the ITW on the server itself (off-hours)?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
I saw the KB article that referenced that issue but it looked as though it
was fixed in SP1 of Version 7. We're running SP3 of Version 2000. I used
the client utilities from a separate client machine here in the states. I
had a colleague remote desktop to the SQL Server itself running the tools on
the local SQL Server and he received the same errors that I'm getting.
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uMu8ZbIjFHA.3784@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
>> Hi,
>> I'm trying to use the ITW for one of our German servers. I have
>> created an input file using Profiler that captures the
>> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
>> using the input file I recieve the following error:
>> "The workload does not contain any events or queries that can be tuned
>> against the current database. Check to see that the trace contains
>> valid events or that the SQL script contains valid queries and you
>> are tuning against the right database"
>> If I select only certain tables to analyze I recieve the following
>> error:
>> "There are no events in the workload. Either the trace file
>> contained no SQL batch or RPC events or the SQL script contained no
>> SQL queries.
>> I have run a broad trace and a narrow trace using the specific
>> database id as a filter.
>> Both using a file and a table return the same errors.
>> If I use the Query Analyzer Selection option with the ITW with a
>> specific query from the input file then the ITW does work properly. Also,
>> the size of the input file is < 100 rows (just a test at this
>> point).
>> It looks as though the ITW has an issue with the code of one or more
>> of the statements in the input file. Is this an accurate assumption?
>> Does anyone know how to resolve this issue so that I can use ITW with
>> many statements as opposed to using only a few at a time?
>> Thanks
>> Jerry
> There used to be a problem with SQL Server 7.0 with running the ITW on a
> client PC using a different locale than the one on the server. Have you
> tried running the ITW on the server itself (off-hours)?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> David,
> I saw the KB article that referenced that issue but it looked as
> though it was fixed in SP1 of Version 7. We're running SP3 of
> Version 2000. I used the client utilities from a separate client
> machine here in the states. I had a colleague remote desktop to the
> SQL Server itself running the tools on the local SQL Server and he
> received the same errors that I'm getting.
> Jerry
>
Have you examined the contents of the workload file to see what was
actually captured? If so, what happens if you copy one of the commands
and tune is manually from Query Analyzer. Does it work? How many
workload queries did you tell the ITW to examine in the workload file?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yea that is my approach right now. I'm copy and pasting individual
statements into QA using the QAS option. Haven't found the culprit yet -
about 90 left to go.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eOlxUkIjFHA.2904@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
>> David,
>> I saw the KB article that referenced that issue but it looked as
>> though it was fixed in SP1 of Version 7. We're running SP3 of
>> Version 2000. I used the client utilities from a separate client
>> machine here in the states. I had a colleague remote desktop to the
>> SQL Server itself running the tools on the local SQL Server and he
>> received the same errors that I'm getting.
>> Jerry
> Have you examined the contents of the workload file to see what was
> actually captured? If so, what happens if you copy one of the commands and
> tune is manually from Query Analyzer. Does it work? How many workload
> queries did you tell the ITW to examine in the workload file?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com