I am trying the export data function on regular job..
how can i export data to different file name based on the date or time?
currently, it exports to the data and archieve at the same file.
http://www.sqldts.com/default.aspx?200
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Test" <TEst@.test.com> wrote in message
news:eGUzISw7EHA.3076@.TK2MSFTNGP15.phx.gbl...
>I am trying the export data function on regular job..
> how can i export data to different file name based on the date or time?
> currently, it exports to the data and archieve at the same file.
>
>
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Tuesday, March 27, 2012
Export data to excel file
I am trying the export data function on regular job..
how can i export data to different file name based on the date or time?
currently, it exports to the data and archieve at the same file.http://www.sqldts.com/default.aspx?200
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Test" <TEst@.test.com> wrote in message
news:eGUzISw7EHA.3076@.TK2MSFTNGP15.phx.gbl...
>I am trying the export data function on regular job..
> how can i export data to different file name based on the date or time?
> currently, it exports to the data and archieve at the same file.
>
>
how can i export data to different file name based on the date or time?
currently, it exports to the data and archieve at the same file.http://www.sqldts.com/default.aspx?200
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Test" <TEst@.test.com> wrote in message
news:eGUzISw7EHA.3076@.TK2MSFTNGP15.phx.gbl...
>I am trying the export data function on regular job..
> how can i export data to different file name based on the date or time?
> currently, it exports to the data and archieve at the same file.
>
>
Export data to excel file
I am trying the export data function on regular job..
how can i export data to different file name based on the date or time?
currently, it exports to the data and archieve at the same file.http://www.sqldts.com/default.aspx?200
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Test" <TEst@.test.com> wrote in message
news:eGUzISw7EHA.3076@.TK2MSFTNGP15.phx.gbl...
>I am trying the export data function on regular job..
> how can i export data to different file name based on the date or time?
> currently, it exports to the data and archieve at the same file.
>
>sql
how can i export data to different file name based on the date or time?
currently, it exports to the data and archieve at the same file.http://www.sqldts.com/default.aspx?200
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Test" <TEst@.test.com> wrote in message
news:eGUzISw7EHA.3076@.TK2MSFTNGP15.phx.gbl...
>I am trying the export data function on regular job..
> how can i export data to different file name based on the date or time?
> currently, it exports to the data and archieve at the same file.
>
>sql
Monday, March 26, 2012
Export data into a script as "insert into"s
Hello,
I am using a Web based front end to a SQL Server db installed at my host that only lets me import data into tables either manually (in a 'datasheet' view) or with a SQL query.
Does anybody know if I can export data from my local db as a script such as
insert into TestTable(Id, Name) values ("1", "Test1")
insert into TestTable(Id, Name) values ("2", "Test2")
.
.
.
... so that I can run this script on the Web front end and insert data into the db at my host?
Many thanks in advance.Here's a link to some stored procedures you might find useful. I think the DataAs InsCommand may be what you are looking for.
http://www.mssqlcity.com/Scripts/scrImpExp.htm|||Thanks for your help Fred.
That stored procedure mostly does the job but looks like it needs a few slight mods because some data types aren't implemented (such as text which is written out as 'None Yet')...
I am using a Web based front end to a SQL Server db installed at my host that only lets me import data into tables either manually (in a 'datasheet' view) or with a SQL query.
Does anybody know if I can export data from my local db as a script such as
insert into TestTable(Id, Name) values ("1", "Test1")
insert into TestTable(Id, Name) values ("2", "Test2")
.
.
.
... so that I can run this script on the Web front end and insert data into the db at my host?
Many thanks in advance.Here's a link to some stored procedures you might find useful. I think the DataAs InsCommand may be what you are looking for.
http://www.mssqlcity.com/Scripts/scrImpExp.htm|||Thanks for your help Fred.
That stored procedure mostly does the job but looks like it needs a few slight mods because some data types aren't implemented (such as text which is written out as 'None Yet')...
Wednesday, March 7, 2012
Expand table rows based on two column ranges
Hi,
I've got a table I'm working with in SQL (through a SAS interface) with the following structure: There is a primary key and for each primary key there are two columns that together constitute a range of values for the primary key. The first column is the start value, the second is the edn value. Here's a quick example:
prim_key startvalue endvalue
1 1023 1026
2 1028 1033
3 1036 1038
What I want to do is expand the table so that each value associated with a primary key is contained on its own row as shown in the table below. I've tried worked with sequences, but this doesn't appear to be the way to go. Does anyone have any suggestions on how I might accomplish this task?
prim_key value
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.
You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:
select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;
You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:
select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?
Originally posted by andrewst
What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.
You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:
select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;
You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:
select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?please, no loops, okay?
you can generate a million integers from just the integers 0 though 9 like this:create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);
select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers uand then left join to your tables as desired|||Pipelined function may come in handy here...
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> CREATE TYPE numbers_table AS TABLE OF NUMBER (10);
2 /
Type created.
SQL> CREATE OR REPLACE FUNCTION expand (
2 start_value IN NUMBER,
3 end_value IN NUMBER)
4 RETURN numbers_table PIPELINED
5 AS
6 BEGIN
7 FOR i IN start_value..end_value LOOP
8 pipe row (i);
9 END LOOP;
10 RETURN;
11 END;
12 /
Function created.
SQL> CREATE TABLE table_name (
2 prim_key NUMBER,
3 startvalue NUMBER,
4 endvalue NUMBER);
Table created.
SQL> INSERT INTO table_name VALUES (1, 1023, 1026);
1 row created.
SQL> INSERT INTO table_name VALUES (2, 1028, 1033);
1 row created.
SQL> INSERT INTO table_name VALUES (3, 1036, 1038);
1 row created.
SQL> SELECT prim_key, column_value
2 FROM table_name, TABLE (expand (startvalue, endvalue));
PRIM_KEY COLUMN_VALUE
---- ----
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038
13 rows selected.
SQL>
I've got a table I'm working with in SQL (through a SAS interface) with the following structure: There is a primary key and for each primary key there are two columns that together constitute a range of values for the primary key. The first column is the start value, the second is the edn value. Here's a quick example:
prim_key startvalue endvalue
1 1023 1026
2 1028 1033
3 1036 1038
What I want to do is expand the table so that each value associated with a primary key is contained on its own row as shown in the table below. I've tried worked with sequences, but this doesn't appear to be the way to go. Does anyone have any suggestions on how I might accomplish this task?
prim_key value
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.
You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:
select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;
You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:
select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?
Originally posted by andrewst
What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.
You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:
select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;
You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:
select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;|||I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?please, no loops, okay?
you can generate a million integers from just the integers 0 though 9 like this:create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);
select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers uand then left join to your tables as desired|||Pipelined function may come in handy here...
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> CREATE TYPE numbers_table AS TABLE OF NUMBER (10);
2 /
Type created.
SQL> CREATE OR REPLACE FUNCTION expand (
2 start_value IN NUMBER,
3 end_value IN NUMBER)
4 RETURN numbers_table PIPELINED
5 AS
6 BEGIN
7 FOR i IN start_value..end_value LOOP
8 pipe row (i);
9 END LOOP;
10 RETURN;
11 END;
12 /
Function created.
SQL> CREATE TABLE table_name (
2 prim_key NUMBER,
3 startvalue NUMBER,
4 endvalue NUMBER);
Table created.
SQL> INSERT INTO table_name VALUES (1, 1023, 1026);
1 row created.
SQL> INSERT INTO table_name VALUES (2, 1028, 1033);
1 row created.
SQL> INSERT INTO table_name VALUES (3, 1036, 1038);
1 row created.
SQL> SELECT prim_key, column_value
2 FROM table_name, TABLE (expand (startvalue, endvalue));
PRIM_KEY COLUMN_VALUE
---- ----
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038
13 rows selected.
SQL>
Friday, February 24, 2012
ExecutionLog Fields
Is there a document that details what the fields in the ExecutionLog
represent. For example, what is the RowCount based on? Or how are the
TimeDataRetrieval, TimeProcessing & TimeRendering fields calculated?You may want to check this related thread for an explanation of execution
log times:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=599418b6-d837-446b-b02b-26394c4a8a7b&sloc=en-us
The RowCount column shows the total number of rows read in _all_ datasets
during the report execution. This includes also dataset rows read for
query-based report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Propwash" <scott.tyree@.crossmark.com> wrote in message
news:1115137030.760735.158180@.o13g2000cwo.googlegroups.com...
> Is there a document that details what the fields in the ExecutionLog
> represent. For example, what is the RowCount based on? Or how are the
> TimeDataRetrieval, TimeProcessing & TimeRendering fields calculated?
>|||Ok, just to make sure I am fully understanding. If I have a SP that
joins three tables (A,B,C), then my RowCound will be the sum of tables
A, B & C plus the resulting recordset. Is this correct?|||No. The RowCount represents only the number of rows in the resulting record
set. If you join multiple table into one dataset inside the stored
procedure, it will not influence the RowCount.
However, if you use the record set in a report parameter and in the report,
you may say a higher count than if you only use it in the report.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Propwash" <scott.tyree@.crossmark.com> wrote in message
news:1115209799.601180.4530@.g14g2000cwa.googlegroups.com...
> Ok, just to make sure I am fully understanding. If I have a SP that
> joins three tables (A,B,C), then my RowCound will be the sum of tables
> A, B & C plus the resulting recordset. Is this correct?
>
represent. For example, what is the RowCount based on? Or how are the
TimeDataRetrieval, TimeProcessing & TimeRendering fields calculated?You may want to check this related thread for an explanation of execution
log times:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=599418b6-d837-446b-b02b-26394c4a8a7b&sloc=en-us
The RowCount column shows the total number of rows read in _all_ datasets
during the report execution. This includes also dataset rows read for
query-based report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Propwash" <scott.tyree@.crossmark.com> wrote in message
news:1115137030.760735.158180@.o13g2000cwo.googlegroups.com...
> Is there a document that details what the fields in the ExecutionLog
> represent. For example, what is the RowCount based on? Or how are the
> TimeDataRetrieval, TimeProcessing & TimeRendering fields calculated?
>|||Ok, just to make sure I am fully understanding. If I have a SP that
joins three tables (A,B,C), then my RowCound will be the sum of tables
A, B & C plus the resulting recordset. Is this correct?|||No. The RowCount represents only the number of rows in the resulting record
set. If you join multiple table into one dataset inside the stored
procedure, it will not influence the RowCount.
However, if you use the record set in a report parameter and in the report,
you may say a higher count than if you only use it in the report.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Propwash" <scott.tyree@.crossmark.com> wrote in message
news:1115209799.601180.4530@.g14g2000cwa.googlegroups.com...
> Ok, just to make sure I am fully understanding. If I have a SP that
> joins three tables (A,B,C), then my RowCound will be the sum of tables
> A, B & C plus the resulting recordset. Is this correct?
>
Friday, February 17, 2012
Execution plan for update
Hi,
Please check two statements below, one is Update, and another is Select
statement based on that Update. Why they have different execution plan?
Problem is that Update statement first joins two titles tables instead to
first join titles and sales as Select does. It makes me problem on large
tables as Update statement never ends (while Select finishes in few
seconds).
I tried to use join hints but Update statement does not allow them.
I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
Thanks in advance
Nikola Milic
--UPDATE StmtText
UPDATE pubs.dbo.titles
SET royalty = STT.royalty -- meaningless, just for test
--SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--UPDATE ExecutionPlan Text
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
SET:([titles].[royalty]=[titles].[royalty]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
| |--Clustered Index
S
(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
|--Index
S
(OBJECT:([pubs].[dbo].[sales].[titleidind]),
SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
--SELECT StmtText
SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--SELECT ExecutionPlan Text
|--Hash Match(Inner Join, HASH:([titles].[title_id])=([sales].[title_id]),
RESIDUAL:([sales].[title_id]=[titles].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
|--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC, [sales].[qty]
ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
|--Clustered Index
S
(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)Hi Nikola,
The first impression I have when I look at your SQL is that you are
overcomplicating things. Are these actual SQL statements you use or are
these just samples?
Your SELECT could be rewritten as follows with the same result ifI'm not
mistaken:
SELECT T.*
FROM pubs.dbo.titles T
JOIN pubs.dbo.sales STT
ON T.title_id = STT.title_id
HTH
Karl Gram
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
> Hi,
> Please check two statements below, one is Update, and another is Select
> statement based on that Update. Why they have different execution plan?
> Problem is that Update statement first joins two titles tables instead to
> first join titles and sales as Select does. It makes me problem on large
> tables as Update statement never ends (while Select finishes in few
> seconds).
> I tried to use join hints but Update statement does not allow them.
> I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
> Thanks in advance
> Nikola Milic
>
>
> --UPDATE StmtText
> UPDATE pubs.dbo.titles
> SET royalty = STT.royalty -- meaningless, just for test
> --SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
> --UPDATE ExecutionPlan Text
> |--Clustered Index
> Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
> SET:([titles].[royalty]=[titles].[royalty]))
> |--Top(ROWCOUNT est 0)
> |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
> | |--Clustered Index
> S
(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
> |--Index
> S
(OBJECT:([pubs].[dbo].[sales].[titleidind]),
> SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
>
>
> --SELECT StmtText
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
>
> --SELECT ExecutionPlan Text
> |--Hash Match(Inner Join,
> HASH:([titles].[title_id])=([sales].[title_id]),
> RESIDUAL:([sales].[title_id]=[titles].[title_id]))
> |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
> |--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC,
> [sales].[qty] ASC))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
> |--Clustered Index
> S
(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)
>|||UPDATE Titles
SET royalty
= (SELECT S.royalty
FROM Sales AS S
WHERE Titles.title_id = S.title_id);
Avoid the unpredictable proprietary syntax needless complexity.|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
My question is why Update statement makes different execution plan from
Select statement? Please note that I'm using here SQL Server syntax to make
Update statement.
Regards
Nikola
"Karl Gram" <karl@.gramonline.nl> wrote in message
news:%2337%23SL%23WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hi Nikola,
> The first impression I have when I look at your SQL is that you are
> overcomplicating things. Are these actual SQL statements you use or are
> these just samples?
> Your SELECT could be rewritten as follows with the same result ifI'm not
> mistaken:
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN pubs.dbo.sales STT
> ON T.title_id = STT.title_id
> --
> HTH
> Karl Gram
>
> "Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
> news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
>|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
Also, please note that I'm using here SQL Server syntax to make Update
statement. My question is why Update statement makes different execution
plan from Select statement?
Your SQL does not work. Table S does not have royalty column.
Regards
Nikola
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116458847.966216.212830@.z14g2000cwz.googlegroups.com...
> UPDATE Titles
> SET royalty
> = (SELECT S.royalty
> FROM Sales AS S
> WHERE Titles.title_id = S.title_id);
> Avoid the unpredictable proprietary syntax needless complexity.
>
Please check two statements below, one is Update, and another is Select
statement based on that Update. Why they have different execution plan?
Problem is that Update statement first joins two titles tables instead to
first join titles and sales as Select does. It makes me problem on large
tables as Update statement never ends (while Select finishes in few
seconds).
I tried to use join hints but Update statement does not allow them.
I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
Thanks in advance
Nikola Milic
--UPDATE StmtText
UPDATE pubs.dbo.titles
SET royalty = STT.royalty -- meaningless, just for test
--SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--UPDATE ExecutionPlan Text
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
SET:([titles].[royalty]=[titles].[royalty]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
| |--Clustered Index
S
SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
|--Index
S
SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
--SELECT StmtText
SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--SELECT ExecutionPlan Text
|--Hash Match(Inner Join, HASH:([titles].[title_id])=([sales].[title_id]),
RESIDUAL:([sales].[title_id]=[titles].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
|--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC, [sales].[qty]
ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
|--Clustered Index
S
SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)Hi Nikola,
The first impression I have when I look at your SQL is that you are
overcomplicating things. Are these actual SQL statements you use or are
these just samples?
Your SELECT could be rewritten as follows with the same result ifI'm not
mistaken:
SELECT T.*
FROM pubs.dbo.titles T
JOIN pubs.dbo.sales STT
ON T.title_id = STT.title_id
HTH
Karl Gram
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
> Hi,
> Please check two statements below, one is Update, and another is Select
> statement based on that Update. Why they have different execution plan?
> Problem is that Update statement first joins two titles tables instead to
> first join titles and sales as Select does. It makes me problem on large
> tables as Update statement never ends (while Select finishes in few
> seconds).
> I tried to use join hints but Update statement does not allow them.
> I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
> Thanks in advance
> Nikola Milic
>
>
> --UPDATE StmtText
> UPDATE pubs.dbo.titles
> SET royalty = STT.royalty -- meaningless, just for test
> --SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
> --UPDATE ExecutionPlan Text
> |--Clustered Index
> Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
> SET:([titles].[royalty]=[titles].[royalty]))
> |--Top(ROWCOUNT est 0)
> |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
> | |--Clustered Index
> S
> SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
> |--Index
> S
> SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
>
>
> --SELECT StmtText
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
>
> --SELECT ExecutionPlan Text
> |--Hash Match(Inner Join,
> HASH:([titles].[title_id])=([sales].[title_id]),
> RESIDUAL:([sales].[title_id]=[titles].[title_id]))
> |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
> |--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC,
> [sales].[qty] ASC))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
> |--Clustered Index
> S
> SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)
>|||UPDATE Titles
SET royalty
= (SELECT S.royalty
FROM Sales AS S
WHERE Titles.title_id = S.title_id);
Avoid the unpredictable proprietary syntax needless complexity.|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
My question is why Update statement makes different execution plan from
Select statement? Please note that I'm using here SQL Server syntax to make
Update statement.
Regards
Nikola
"Karl Gram" <karl@.gramonline.nl> wrote in message
news:%2337%23SL%23WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hi Nikola,
> The first impression I have when I look at your SQL is that you are
> overcomplicating things. Are these actual SQL statements you use or are
> these just samples?
> Your SELECT could be rewritten as follows with the same result ifI'm not
> mistaken:
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN pubs.dbo.sales STT
> ON T.title_id = STT.title_id
> --
> HTH
> Karl Gram
>
> "Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
> news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
>|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
Also, please note that I'm using here SQL Server syntax to make Update
statement. My question is why Update statement makes different execution
plan from Select statement?
Your SQL does not work. Table S does not have royalty column.
Regards
Nikola
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116458847.966216.212830@.z14g2000cwz.googlegroups.com...
> UPDATE Titles
> SET royalty
> = (SELECT S.royalty
> FROM Sales AS S
> WHERE Titles.title_id = S.title_id);
> Avoid the unpredictable proprietary syntax needless complexity.
>
Subscribe to:
Posts (Atom)