Thursday, March 29, 2012
Correlated Subqueries?
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LPA few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:
>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20
2 4 B 40
3 4 C
4 9 ABC 200
5 8 X
6 8 Y
7 8 Z
8 9 XYZ 210
9 9 ABC-XYZ
10 5 X1
11 5 X2
12 12 PQ 90
13 12 P 50
14 12 Q 40
15 15 MD
16 15 M 240
17 15 D 110
18 17 D1 60
19 17 D2 70
20 16 M1 50
Ideally I want something like this.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20 470
2 4 B 40 470
3 4 C 470
4 9 ABC 200 470
5 8 X 470
6 8 Y 470
7 8 Z 470
8 9 XYZ 210 470
9 9 ABC-XYZ 470
10 5 X1 470
11 5 X2 470
12 12 PQ 90 180
13 12 P 50 180
14 12 Q 40 180
15 15 MD 530
16 15 M 240 530
17 15 D 110 530
18 17 D1 60 530
19 17 D2 70 530
20 16 M1 50 530
"Steve Kass" wrote:
> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
> >I need to loop through several rows and find matches for values and the
> >aggregate a different column. Being a VB type and not as much the sql type
> >my approach would be to loop through the rows and be done with it. But that
> >does not fit here I need to do it in stored procedure. I was told that
> >correlated sub queries are the ticket to do what I wish but I can't seem to
> >get to to work.
> >
> >My sql looks like this:
> >use Prototype_BIDW_TALX
> >--Variable Declaration
> >DECLARE @.ParentID int
> >DECLARE @.UltPID int
> >DECLARE @.UltParentName varchar (50)
> >DECLARE @.TotalEEs int
> >DECLARE @.UltTopAcct bit
> >
> >set @.ParentID = (select distinct [Parent ID] from Customers)
> >
> >SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> >sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
> >WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> >group by [Company ID] , [Name],[accttype]
> >
> >I get this error:
> >Sub query returned more than 1 value. This is not permitted when the sub
> >query follows =, !=, <, <= , >, >= or when the sub query is used as an
> >expression.
> >
> >Isn't the correlated sub query supposed to return more than one value so you
> >can basically loop through?
> >
> >What am I missing?
> >
> >LP
> >
> >
> >
> >
>|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20
>2 4 B 40
>3 4 C
>4 9 ABC 200
>5 8 X
>6 8 Y
>7 8 Z
>8 9 XYZ 210
>9 9 ABC-XYZ
>10 5 X1
>11 5 X2
>12 12 PQ 90
>13 12 P 50
>14 12 Q 40
>15 15 MD
>16 15 M 240
>17 15 D 110
>18 17 D1 60
>19 17 D2 70
>20 16 M1 50
>
>Ideally I want something like this.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20 470
>2 4 B 40 470
>3 4 C 470
>4 9 ABC 200 470
>5 8 X 470
>6 8 Y 470
>7 8 Z 470
>8 9 XYZ 210 470
>9 9 ABC-XYZ 470
>10 5 X1 470
>11 5 X2 470
>12 12 PQ 90 180
>13 12 P 50 180
>14 12 Q 40 180
>15 15 MD 530
>16 15 M 240 530
>17 15 D 110 530
>18 17 D1 60 530
>19 17 D2 70 530
>20 16 M1 50 530
>
>"Steve Kass" wrote:
>
>>LP,
>> I don't see a correlated subquery here. You have two subqueries,
>>(select distinct [Parent ID] from Customers) and (select distinct
>>[CompanyParent ID] from Customers), both of which look like standalone
>>queries, and one or the other returns more than one row, apparently. A
>>subquery is correlated if it depends on some column value from the outer
>>query, as in
>>select ...
>>from someTable as T1
>>where [condition contains a query like:] (select ... from anywhere where
>>[condition or expression in select list includes T1.somecolumn])
>>It's very hard to guess what you want here. If you could give the table
>>declarations, sample data, and show the output you are looking for, it
>>would help.
>>Steve Kass
>>Drew University
>>LP wrote:
>>
>>I need to loop through several rows and find matches for values and the
>>aggregate a different column. Being a VB type and not as much the sql type
>>my approach would be to loop through the rows and be done with it. But that
>>does not fit here I need to do it in stored procedure. I was told that
>>correlated sub queries are the ticket to do what I wish but I can't seem to
>>get to to work.
>>My sql looks like this:
>>use Prototype_BIDW_TALX
>>--Variable Declaration
>>DECLARE @.ParentID int
>>DECLARE @.UltPID int
>>DECLARE @.UltParentName varchar (50)
>>DECLARE @.TotalEEs int
>>DECLARE @.UltTopAcct bit
>>set @.ParentID = (select distinct [Parent ID] from Customers)
>>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>>group by [Company ID] , [Name],[accttype]
>>I get this error:
>>Sub query returned more than 1 value. This is not permitted when the sub
>>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>>expression.
>>Isn't the correlated sub query supposed to return more than one value so you
>>can basically loop through?
>>What am I missing?
>>LP
>>
>>
>>
Correlated Subqueries?
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LP
A few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>
|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:
>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>
|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.
|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20
24B40
34C
49ABC200
58X
68Y
78Z
89XYZ210
99ABC-XYZ
105X1
115X2
1212PQ90
1312P50
1412Q40
1515MD
1615M240
1715D110
1817D160
1917D270
2016M150
Ideally I want something like this.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20470
24B40470
34C470
49ABC200470
58X470
68Y470
78Z470
89XYZ210470
99ABC-XYZ470
105X1470
115X2470
1212PQ90180
1312P50180
1412Q40180
1515MD530
1615M240530
1715D110530
1817D160530
1917D270530
2016M150530
"Steve Kass" wrote:
> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
>
|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
[vbcol=seagreen]
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20
>24B40
>34C
>49ABC200
>58X
>68Y
>78Z
>89XYZ210
>99ABC-XYZ
>105X1
>115X2
>1212PQ90
>1312P50
>1412Q40
>1515MD
>1615M240
>1715D110
>1817D160
>1917D270
>2016M150
>
>Ideally I want something like this.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20470
>24B40470
>34C470
>49ABC200470
>58X470
>68Y470
>78Z470
>89XYZ210470
>99ABC-XYZ470
>105X1470
>115X2470
>1212PQ90180
>1312P50180
>1412Q40180
>1515MD530
>1615M240530
>1715D110530
>1817D160530
>1917D270530
>2016M150530
>
>"Steve Kass" wrote:
>
Tuesday, March 20, 2012
Copying rows with all child rows
I have some complicated database with many relational tables and one root
table (i.e. has no FK)
What I need to do is to copy a row inside the root table then copy all child
rows and change references in them to refer to the copied row in the root
table, and then copy the child rows from child rows and make them referring
to the copied root child row . and so on until the last level.
I thought about making a strong typed DataSet for this database and then
loading the relevant rows, call MemberwiseClone on it and then update the
DataSet, anyway this way is very inefficient because I need to retrieve all
data from the sql server to the computer running .Net and then sending the
copied data back.
Does anybody know a way to do this job using just T-SQL ?
Thanks in advance
Stefan RosiI'm not sure of the details of what you're asking. but if I understand
correctly this is the direction I'd try...
INSERT RootCopy
FROM Root
WHERE Rootexpression
INSERT ChildCopy
FROM Child
JOIN Root
ON Root.PK = Child.FK
WHERE Rootexpression
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Stefan Rosi" <Stefan.Rosie_ReplaceWithAt_gmail.com> wrote in message
news:OxBkPic0EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> Hallo NG,
>
> I have some complicated database with many relational tables and one root
> table (i.e. has no FK)
> What I need to do is to copy a row inside the root table then copy all
> child
> rows and change references in them to refer to the copied row in the root
> table, and then copy the child rows from child rows and make them
> referring
> to the copied root child row . and so on until the last level.
> I thought about making a strong typed DataSet for this database and then
> loading the relevant rows, call MemberwiseClone on it and then update the
> DataSet, anyway this way is very inefficient because I need to retrieve
> all
> data from the sql server to the computer running .Net and then sending the
> copied data back.
> Does anybody know a way to do this job using just T-SQL ?
>
> Thanks in advance
> Stefan Rosi
>sql
Copying rows with all child rows
I have some complicated database with many relational tables and one root
table (i.e. has no FK)
What I need to do is to copy a row inside the root table then copy all child
rows and change references in them to refer to the copied row in the root
table, and then copy the child rows from child rows and make them referring
to the copied root child row . and so on until the last level.
I thought about making a strong typed DataSet for this database and then
loading the relevant rows, call MemberwiseClone on it and then update the
DataSet, anyway this way is very inefficient because I need to retrieve all
data from the sql server to the computer running .Net and then sending the
copied data back.
Does anybody know a way to do this job using just T-SQL ?
Thanks in advance
Stefan Rosi
I'm not sure of the details of what you're asking. but if I understand
correctly this is the direction I'd try...
INSERT RootCopy
FROM Root
WHERE Rootexpression
INSERT ChildCopy
FROM Child
JOIN Root
ON Root.PK = Child.FK
WHERE Rootexpression
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Stefan Rosi" <Stefan.Rosie_ReplaceWithAt_gmail.com> wrote in message
news:OxBkPic0EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> Hallo NG,
>
> I have some complicated database with many relational tables and one root
> table (i.e. has no FK)
> What I need to do is to copy a row inside the root table then copy all
> child
> rows and change references in them to refer to the copied row in the root
> table, and then copy the child rows from child rows and make them
> referring
> to the copied root child row . and so on until the last level.
> I thought about making a strong typed DataSet for this database and then
> loading the relevant rows, call MemberwiseClone on it and then update the
> DataSet, anyway this way is very inefficient because I need to retrieve
> all
> data from the sql server to the computer running .Net and then sending the
> copied data back.
> Does anybody know a way to do this job using just T-SQL ?
>
> Thanks in advance
> Stefan Rosi
>
Copying rows from multiple tables to a single table
Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
- --
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
Try:
Code Snippet
insert into table_master([uniqueid], cat1, cat2, cat3)
select
[id],
[Cat1],
[Cat2],
[Cat3]
from
(
select [id], 'Cat1' as table_name, lastdate from dbo.Cat1
union all
select [id], 'Cat2' as table_name, lastdate from dbo.Cat2
union all
select [id], 'Cat3' as table_name, lastdate from dbo.Cat3
) as a
pivot
(
max(lastdate)
for table_name in ([Cat1], [Cat2], [Cat3])
) as pvt
go
AMB
|||Can you use full outter join style?
CREATE Table #Cat1
(
id int,
val varchar(10)
)
insert into #cat1 (id, val) values (1, 'D1')
insert into #cat1 (id, val) values (2, 'D2')
insert into #cat1 (id, val) values (3, 'D3')
CREATE Table #Cat2
(
id int,
val varchar(10)
)
insert into #cat2 (id, val) values (2, 'D4')
insert into #cat2 (id, val) values (3, 'D5')
insert into #cat2 (id, val) values (4, 'D6')
SELECT
CASE
WHEN C1.Id IS NOT NULL THEN C1.Id
WHEN C2.Id IS NOT NULL THEN C2.Id
ELSE NULL
END AS [Id],
C1.val,
C2.val
FROM #Cat1 c1
FULL OUTER JOIN #cat2 C2 ON
(C1.Id = C2.Id)
DROP TABLE #Cat1
DROP TABLE #Cat2
|||
Code Snippet
Create Table #cat1 (
id int,
[LastDate] datetime
);
Insert Into #cat1 Values('1','01/01/07');
Insert Into #cat1 Values('2','01/02/07');
Insert Into #cat1 Values('3','01/03/07');
Create Table #cat2 (
id int,
[LastDate] datetime
);
Insert Into #cat2 Values('2','01/02/07');
Insert Into #cat2 Values('3','01/03/07');
Insert Into #cat2 Values('4','01/04/07');
SELECT c.id, c.LastDate, d.LastDate FROM (SELECT a.id, b.LastDate FROM (SELECT id FROM #cat1
union
SELECT id FROM #cat2) a LEFT JOIN #cat1 b ON a.id=b.id) c LEFT JOIN #cat2 d ON c.id=d.id
|||Hi AMB and limon
Thank you bothe very much for your queries.
Both works perfect.
AMB: Can you pelase explain me why u have used Pivt and what is the impact of it in terms of performace and also can u please explain me why you have used max(lastUpdate) and what is the use of it
Limon : Though it works fine i feel the query is bit long.
My concern is that i may have N number of category tables and i have to merge them into a single table.
So i need to build the a query dynamically based on the number of the tables and merge them.
Can any one please explaine me which one executes faster as i might have millions of rows in the category table
Thanks for your time
~Mohan
|||Dave,
This query inserts null if the ID doesn't exist in botht the tables.
Thanks for your time
~Mohan
|||Hi mohandbabud,
why did I use pivot?
Because that is what you are doing. You are pivoting rows to columns. When using pivot operator, you need an aggregation function, and It does not make sense to use SUM, AVG, etc, with a [datetime] data type. you could use MIN also. BTW, I supposed that the [id] is unique in each category table.
About which one one will execute faster, I do not know, because both queries are doing heavy stuff. You can tell us when you have finished.
AMB
copying large SQL SERVER tables between DB's
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
aviIf you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
--
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
copying large SQL SERVER tables between DB's
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
aviIf you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
Monday, March 19, 2012
copying large SQL SERVER tables between DB's
I have a simple question... how does one copy a LARGE table (say 100m rows)
from one database to another in SQL Server 2000?
Using the copy table feature in DTS is great - but can it be done from a
T-SQL interface (say a stored procedure or something)?
the bulk insert/copy features seem to only work with data transfers between
a DB and an external data file.
Any external software?
thanks
avi
If you set up linked servers, you could run TSQL commands like INSERT INTO
and so forth.
With 100million rows, you may want to batch those TSQL commands out into
several smaller transactions.
For example;
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 0 and 100000
COMMIT
BEGIN TRAN
INSERT INTO dest_server.database.owner.table
FROM source_server.database.owner.table
WHERE source_server.database.owner.table.somefield between 100000 and 200000
COMMIT
and so forth
Rick Sawtell
MCT, MCSD, MCDBA
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
|||Take a look at BCP and BULK INSERT in BooksOnLine. The native mode will
probably give fastest and cleanest results.
Andrew J. Kelly SQL MVP
"Avi Perez" <avi.perez@.irisbi.com> wrote in message
news:e7sHr%23rjEHA.3972@.tk2msftngp13.phx.gbl...
> Hi
> I have a simple question... how does one copy a LARGE table (say 100m
rows)
> from one database to another in SQL Server 2000?
> Using the copy table feature in DTS is great - but can it be done from a
> T-SQL interface (say a stored procedure or something)?
> the bulk insert/copy features seem to only work with data transfers
between
> a DB and an external data file.
> Any external software?
> thanks
> avi
>
Copying large sets of data in same database
I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.
What is the best way of doing this?
I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?
Thanks
If you are going to use SSIS for this, I think your best performance option will be to use an Execute SQL task and then perform the "INSERT INTO ... SELECT" statement.|||fleo wrote:
I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.
What is the best way of doing this?
I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?
Thanks
If they are in the same database then I'd be shocked if anything outperforms INSERT INTO... SELECT...
Try that out and also try out the SSIS dataflow, see how much performance differs (if at all). Let us know as well, I for one would be very interested to know.
You may also want to test if it is any quicker to drop all indexes on the target table prior to loading and then recreating them afterwards.
-Jamie
|||I made a simple comparison between the two:
With 7 millions rows (approximations)
SQL: 13 min.
Data flow task: 26 min.
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
That brings me to the "Update/Insert" example (http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)
How come the SSIS sometimes outperforms pure SQL? Which part is faster? If SSIS performs multiple steps at a time, why can't SQL Server do the same?
Thanks guys!
|||fleo wrote:
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
They do. It's the "Fast load" option.|||
fleo wrote:
I made a simple comparison between the two:
With 7 millions rows (approximations)
SQL: 13 min.
Data flow task: 26 min.
Damn. I'd hoped the difference would have been smaller than that
Which destination adapter did you use?
fleo wrote:
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
Well essentially it does. If you use OLE DB Destination with FastLoad then it uses SQL Server's Bulk Insert mechanism (all explained here: http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx).
The SQL Server Destination should be even quicker though (although it only works if you're running the package on the same server as whre the destination table resides)
fleo wrote:
That brings me to the "Update/Insert" example (http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)
How come the SSIS sometimes outperforms pure SQL? Which part is faster? If SSIS performs multiple steps at a time, why can't SQL Server do the same?
Thanks guys!
There are so many variables in this its impossible to give a definative answer. If its straight table-to-table on the same server though I would expect pure T-SQL to be quicker.
-Jamie
|||I used the OLE DB Destination with the Fast Load option as adapter. I also expected the difference would be smaller...
Thanks for link... I had already read it :) I often go to your blog for info. I ask a lot of questions because my focus is to determine the best implementation for data transfer/manipulation.
My rule of thumb so far was to use SSIS only to transfer data between heterogeneous sources (text file, other DBMS,...). Once the data is in a SQL Server database I always use T-SQL and expect it to perform better than any other method.
But it doesn't seem to always be the case... I'll try to figure that out later on.
Thanks all
|||The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
|||It's a very good point.bkallich wrote:
The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
Luckily we have a identity field we can loop through.
|||
bkallich wrote:
The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
If you set MaxInsertCommitSize to a value larger than the number of rows passing through the destination, then it will perform the inserts in one batch as well.
RowsPerBatch is a query optimizer hint. Nothing more, really.|||
Phil Brammer wrote:
bkallich wrote:
The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
If you set MaxInsertCommitSize to a value larger than the number of rows passing through the destination, then it will perform the inserts in one batch as well.
RowsPerBatch is a query optimizer hint. Nothing more, really.
He told me RowsPerBatch is the fetch size from the source and commit size relates to the destination. Is it correct?
|||From BOL:ROWS_PER_BATCH = rows_per_batch
Indicates the approximate number of rows of data in the data file.
By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.
BATCHSIZE = batch_size = MaxInsertCommitSize in SSIS
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch.|||
I tested the process with different values.
As you mentionned, the MaxCommitSize has the most impact on performance.
With RowsPerBatch = 100 000
MaxCommitSize = 1 -> around 13 000 records/min
MaxCommitSize = 7 000 000 -> 600 000 records/min
RowsPerBatch:
With MaxCommitSize = 100 000 I can't see much difference between RowsPerBatch = 1 and 7 000 000...
Phil, can these properties be set dynamically? They do not seem to be available though expressions nor package configuration. I wanted to create a basic benchmark and pass these values as parameters.
Thanks
|||I don't think you can set those values dynamically. Anyway, post the results of your tests as they may be very interesting. Few months ago I was playing with those values and I never could get more than 10k rows per batch; but I actually did not dig into it.Copying large sets of data in same database
I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.
What is the best way of doing this?
I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?
Thanks
If you are going to use SSIS for this, I think your best performance option will be to use an Execute SQL task and then perform the "INSERT INTO ... SELECT" statement.|||fleo wrote:
I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.
What is the best way of doing this?
I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?
Thanks
If they are in the same database then I'd be shocked if anything outperforms INSERT INTO... SELECT...
Try that out and also try out the SSIS dataflow, see how much performance differs (if at all). Let us know as well, I for one would be very interested to know.
You may also want to test if it is any quicker to drop all indexes on the target table prior to loading and then recreating them afterwards.
-Jamie
|||
I made a simple comparison between the two:
With 7 millions rows (approximations)
SQL: 13 min.
Data flow task: 26 min.
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
That brings me to the "Update/Insert" example (http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)
How come the SSIS sometimes outperforms pure SQL? Which part is faster? If SSIS performs multiple steps at a time, why can't SQL Server do the same?
Thanks guys!
|||fleo wrote:
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
They do. It's the "Fast load" option.|||
fleo wrote:
I made a simple comparison between the two:
With 7 millions rows (approximations)
SQL: 13 min.
Data flow task: 26 min.
Damn. I'd hoped the difference would have been smaller than that
Which destination adapter did you use?
fleo wrote:
I was thinking maybe SSIS had a "Bulk Insert" mechanism to pump data from a table to another...
Well essentially it does. If you use OLE DB Destination with FastLoad then it uses SQL Server's Bulk Insert mechanism (all explained here: http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx).
The SQL Server Destination should be even quicker though (although it only works if you're running the package on the same server as whre the destination table resides)
fleo wrote:
That brings me to the "Update/Insert" example (http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)
How come the SSIS sometimes outperforms pure SQL? Which part is faster? If SSIS performs multiple steps at a time, why can't SQL Server do the same?
Thanks guys!
There are so many variables in this its impossible to give a definative answer. If its straight table-to-table on the same server though I would expect pure T-SQL to be quicker.
-Jamie
|||
I used the OLE DB Destination with the Fast Load option as adapter. I also expected the difference would be smaller...
Thanks for link... I had already read it :) I often go to your blog for info. I ask a lot of questions because my focus is to determine the best implementation for data transfer/manipulation.
My rule of thumb so far was to use SSIS only to transfer data between heterogeneous sources (text file, other DBMS,...). Once the data is in a SQL Server database I always use T-SQL and expect it to perform better than any other method.
But it doesn't seem to always be the case... I'll try to figure that out later on.
Thanks all
|||The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
|||It's a very good point.bkallich wrote:
The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
Luckily we have a identity field we can loop through.
|||
bkallich wrote:
The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with
batch size option inserts fewer rows in a transaction. The later puts
less stress on the system especially if the recovery model is "Simple".
If you set MaxInsertCommitSize to a value larger than the number of rows passing through the destination, then it will perform the inserts in one batch as well.
RowsPerBatch is a query optimizer hint. Nothing more, really.|||
Phil Brammer wrote:
bkallich wrote: The "INSERT INTO... SELECT..." inserts all the rows in one trasaction verses SSIS with batch size option inserts fewer rows in a transaction. The later puts less stress on the system especially if the recovery model is "Simple".
If you set MaxInsertCommitSize to a value larger than the number of rows passing through the destination, then it will perform the inserts in one batch as well.
RowsPerBatch is a query optimizer hint. Nothing more, really.
He told me RowsPerBatch is the fetch size from the source and commit size relates to the destination. Is it correct?
|||From BOL:ROWS_PER_BATCH = rows_per_batch
Indicates the approximate number of rows of data in the data file.
By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.
BATCHSIZE = batch_size = MaxInsertCommitSize in SSIS
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch.|||
I tested the process with different values.
As you mentionned, the MaxCommitSize has the most impact on performance.
With RowsPerBatch = 100 000
MaxCommitSize = 1 -> around 13 000 records/min
MaxCommitSize = 7 000 000 -> 600 000 records/min
RowsPerBatch:
With MaxCommitSize = 100 000 I can't see much difference between RowsPerBatch = 1 and 7 000 000...
Phil, can these properties be set dynamically? They do not seem to be available though expressions nor package configuration. I wanted to create a basic benchmark and pass these values as parameters.
Thanks
|||I don't think you can set those values dynamically. Anyway, post the results of your tests as they may be very interesting. Few months ago I was playing with those values and I never could get more than 10k rows per batch; but I actually did not dig into it.Thursday, March 8, 2012
Copying data across rows
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?
Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?
|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?
|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
Copying data across rows
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
Copying data across rows
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
Wednesday, March 7, 2012
copying all rows from one table into another existing table and overwriting data
any ideas on how i could do this easily?
thanksUSE Northwind
GO
SET NOCOUNT ON
SELECT * INTO myTable1 FROM Employees
GO
-- Have a Look
SELECT * FROM myTable1
-- Make a Backup
SELECT * INTO myTable2 FROM myTable1
GO
-- oops
DELETE FROM myTable1 WHERE EmployeeID BETWEEN 2 AND 6
GO
-- Have a Look
SELECT * FROM myTable1
GO
SET IDENTITY_INSERT myTable1 ON
GO
-- Recover the Rows
INSERT INTO myTable1 (
EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
)
SELECT EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
FROM myTable2
WHERE EmployeeID NOT IN (SELECT EmployeeID
FROM myTable1)
GO
SET IDENTITY_INSERT myTable1 OFF
GO
-- Have a Look
SELECT * FROM myTable1
GO
-- Clean up
DROP TABLE myTable1
DROP TABLE myTable2
GO
Copying a hierarchy of rows
I have this scenario:
Table of Folders: FolderID, FolderName, ParentFolderID
I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
procedure gets three parameters: SourceFolderID, ParentFolderID,
NewFolderName. It creates a new folder whose name and parent are taken
from the parameters. Now comes the hard part - take all the descendant
folders of SourceFolderID and insert new rows with the same hierarchy
under the new folder. The hierarchy is not limited to one level ofcourse...
I'm looking for a solution on SQL Server 2000.
Anyone? :-)
Thanks in advance,
R. GreenRonald
Google for Itzik Ben-Gan's solutions for such kind of query
There are some examples from his articles
CREATE DATABASE testdb
GO
USE testdb
GO
-- Schema and Data of the Employees table
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
-- Scalar functions --
--
-- Getting Ancestor using Recursion
CREATE FUNCTION dbo.ufn_GetAncestor
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
IF @.lvl = 0
RETURN @.empid
RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @.empid),
@.lvl -1)
END
GO
-- Getting Ancestor using a Loop
CREATE FUNCTION dbo.ufn_GetAncestor2
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
DECLARE @.mgrid AS int
SET @.mgrid = @.empid
WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
FROM Employees WHERE empid = @.mgrid
RETURN @.mgrid
END
GO
-- test the dbo.ufn_GetAncestor function
SELECT dbo.ufn_GetAncestor(11, 2)
SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
SELECT E.empname AS employee, A.empname AS ancestor
FROM Employees AS E LEFT OUTER JOIN Employees AS A
ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
GO
-- Calculating an Aggregate of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
-- Calculating the Depth of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees
WHERE mgrid = @.mgrid)
WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
ELSE NULL
END
END
GO
SELECT dbo.ufn_GetSubtreeDepth(1)
GO
-- Table-Valued functions --
--
-- Get a whole Subtree
CREATE FUNCTION ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
-- get leaf nodes of a subtree
SELECT * FROM ufn_GetSubtree(2) AS S
WHERE NOT EXISTS(SELECT * FROM Employees AS E
WHERE E.mgrid = S.empid)
GO
-- Get the Chain of Management Leading to a Certain Employee
CREATE FUNCTION ufn_GetMgmtChain
(
@.empid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int
SET @.lvl = 0
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary, @.lvl
FROM Employees
WHERE empid = @.empid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary, @.lvl
FROM Employees AS E JOIN @.tree AS T
ON E.empid = T.mgrid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT * FROM ufn_GetMgmtChain(14)
ORDER BY lvl DESC
"Ronald Green" <b@.man.com> wrote in message
news:%23a3iMT1vFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have this scenario:
> Table of Folders: FolderID, FolderName, ParentFolderID
> I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
> procedure gets three parameters: SourceFolderID, ParentFolderID,
> NewFolderName. It creates a new folder whose name and parent are taken
> from the parameters. Now comes the hard part - take all the descendant
> folders of SourceFolderID and insert new rows with the same hierarchy
> under the new folder. The hierarchy is not limited to one level
> ofcourse...
> I'm looking for a solution on SQL Server 2000.
> Anyone? :-)
> Thanks in advance,
> R. Green|||Ronald
you can use dynamic sql if you want to change column names dynamically
something like this
declare @.stringvar varchar(100), @.name varchar(100), @.stno int
set @.stringvar = 'select ' + @.name + 'from pis where ' + @.stno + '= 1548'
exec(@.stringvar)
or you can use sp_executesql which has capacity to use parameters
for full discussion read this
http://www.sommarskog.se/dynamic_sql.html
however, it appers to me that it is possible to use plain sql here instead
of dynamic one. if you can post DDL/SAMPLE data we will better suggest you.
Regards
R.D
"Ronald Green" wrote:
> Hi,
> I have this scenario:
> Table of Folders: FolderID, FolderName, ParentFolderID
> I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored
> procedure gets three parameters: SourceFolderID, ParentFolderID,
> NewFolderName. It creates a new folder whose name and parent are taken
> from the parameters. Now comes the hard part - take all the descendant
> folders of SourceFolderID and insert new rows with the same hierarchy
> under the new folder. The hierarchy is not limited to one level ofcourse..
.
> I'm looking for a solution on SQL Server 2000.
> Anyone? :-)
> Thanks in advance,
> R. Green
>|||oops
It is at the wrong place
"R.D" wrote:
> Ronald
> you can use dynamic sql if you want to change column names dynamically
> something like this
> declare @.stringvar varchar(100), @.name varchar(100), @.stno int
> set @.stringvar = 'select ' + @.name + 'from pis where ' + @.stno + '= 1548
'
> exec(@.stringvar)
> or you can use sp_executesql which has capacity to use parameters
> for full discussion read this
> http://www.sommarskog.se/dynamic_sql.html
> however, it appers to me that it is possible to use plain sql here instea
d
> of dynamic one. if you can post DDL/SAMPLE data we will better suggest you
.
> Regards
> R.D
>
>
> "Ronald Green" wrote:
>|||Hi Uri,
I read parts of Itzik's solutions for maintaining hierarchies. Didn't
find anything about *copying* a hierarchy... I figure that copying the
hierarchy is a bit more complicated than aggregates and such on the
hierarchy, so I decided to do the recursion from outside... a lot more
calls to the servers but it'll be faster to write.
Cheers,
R. Green
Uri Dimant wrote:
> Ronald
> Google for Itzik Ben-Gan's solutions for such kind of query
> There are some examples from his articles
>
> CREATE DATABASE testdb
> GO
> USE testdb
> GO
> -- Schema and Data of the Employees table
> CREATE TABLE Employees
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
> CONSTRAINT FK_Employees_mgrid_empid
> FOREIGN KEY(mgrid)
> REFERENCES Employees(empid)
> )
> CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
> INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
> INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
> INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
> INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
> INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
> INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
> INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
> INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
> INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
> INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
> INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
> INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
> INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
> INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
> GO
> --
> -- Scalar functions --
> --
> -- Getting Ancestor using Recursion
> CREATE FUNCTION dbo.ufn_GetAncestor
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> IF @.lvl = 0
> RETURN @.empid
> RETURN dbo.ufn_GetAncestor(
> (SELECT mgrid FROM Employees WHERE empid = @.empid),
> @.lvl -1)
> END
> GO
> -- Getting Ancestor using a Loop
> CREATE FUNCTION dbo.ufn_GetAncestor2
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> DECLARE @.mgrid AS int
> SET @.mgrid = @.empid
> WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
> SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
> FROM Employees WHERE empid = @.mgrid
> RETURN @.mgrid
> END
> GO
> -- test the dbo.ufn_GetAncestor function
> SELECT dbo.ufn_GetAncestor(11, 2)
> SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
> SELECT E.empname AS employee, A.empname AS ancestor
> FROM Employees AS E LEFT OUTER JOIN Employees AS A
> ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
> GO
> -- Calculating an Aggregate of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeSalary
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN (SELECT Salary
> FROM Employees WHERE empid = @.mgrid) +
> CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> (SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> ELSE 0
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeSalary(3)
> GO
> -- Calculating the Depth of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeDepth
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
> ELSE NULL
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeDepth(1)
> GO
> --
> -- Table-Valued functions --
> --
> -- Get a whole Subtree
> CREATE FUNCTION ufn_GetSubtree
> (
> @.mgrid AS int
> )
> RETURNS @.tree table
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT empid, mgrid, empname, salary,
> @.lvl, '.' + CAST(empid AS varchar(10)) + '.'
> FROM Employees
> WHERE empid = @.mgrid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.empid, E.mgrid, E.empname, E.salary,
> @.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
> FROM Employees AS E JOIN @.tree AS T
> ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> GO
> SELECT REPLICATE (' | ', lvl) + empname AS employee
> FROM ufn_GetSubtree(1)
> ORDER BY path
> /*
> employee
> --
> Nancy
> | Andrew
> | | Steven
> | | Michael
> | Janet
> | | Robert
> | | | David
> | | | | James
> | | | Ron
> | | | Dan
> | | Laura
> | | Ann
> | Margaret
> | | Ina
> */
> -- get leaf nodes of a subtree
> SELECT * FROM ufn_GetSubtree(2) AS S
> WHERE NOT EXISTS(SELECT * FROM Employees AS E
> WHERE E.mgrid = S.empid)
> GO
> -- Get the Chain of Management Leading to a Certain Employee
> CREATE FUNCTION ufn_GetMgmtChain
> (
> @.empid AS int
> )
> RETURNS @.tree table
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> lvl int NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int
> SET @.lvl = 0
> INSERT INTO @.tree
> SELECT empid, mgrid, empname, salary, @.lvl
> FROM Employees
> WHERE empid = @.empid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.empid, E.mgrid, E.empname, E.salary, @.lvl
> FROM Employees AS E JOIN @.tree AS T
> ON E.empid = T.mgrid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> GO
> SELECT * FROM ufn_GetMgmtChain(14)
> ORDER BY lvl DESC
> "Ronald Green" <b@.man.com> wrote in message
> news:%23a3iMT1vFHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>
>
Friday, February 24, 2012
COPY to a .txt file
How do I copy the rows in my table into a .txt file comma delimited.
I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
but it does not work
TIA
Roy GourgiThat's because SQL Server is not FoxPro<g>. Take a look at the Import /
Export wizard. You can get to it in Enterprise Manager by choosing Tools /
Import/Export.
Andrew J. Kelly SQL MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:KO45g.43$hR6.20646@.weber.videotron.net...
> Hi,
> How do I copy the rows in my table into a .txt file comma delimited.
> I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
> but it does not work
> TIA
> Roy Gourgi
>|||You can create txt file using SQLCMD.
that is simmiliar to osql utitilty.
"Roy Gourgi"?? ??? ??:
> Hi,
> How do I copy the rows in my table into a .txt file comma delimited.
> I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
> but it does not work
> TIA
> Roy Gourgi
>
>
Copy to a .txt file
How do I copy the rows in my table into a .txt file comma delimited.
I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
but it does not work
TIA
Roy GourgiHi Roy
That does not look like SQL Server syntax, which DBMS are you using? If you
are using SQL Server check out the utilities BCP or DTS to do this for you.
John
"Roy Gourgi" wrote:
> Hi,
> How do I copy the rows in my table into a .txt file comma delimited.
> I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
> but it does not work
> TIA
> Roy Gourgi
>
>
Copy to a .txt file
How do I copy the rows in my table into a .txt file comma delimited.
I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
but it does not work
TIA
Roy GourgiHi Roy
That does not look like SQL Server syntax, which DBMS are you using? If you
are using SQL Server check out the utilities BCP or DTS to do this for you.
John
"Roy Gourgi" wrote:
> Hi,
> How do I copy the rows in my table into a .txt file comma delimited.
> I tried COPY mytable TO '/presentation/myfile' WITH DELIMITER ','
> but it does not work
> TIA
> Roy Gourgi
>
>
Sunday, February 19, 2012
copy the data from one field of one row to the same field of another row
The task seems simple:
There are a few rows in a table that looks like this:
RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...
After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this
info@.test.com,Tester, 1, 07/18/2002
The closest I got to a solution was this:
CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),
AS
UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)
GO
This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.
Can anyone see how to copy selectable data from one row to another?
eWWThe procedure accepts a recipientID to check - did you want it to process all of the recipients in the table at once?|||Not too clear what you want but something like this maybe
update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1
If there can only be one rec with FreshUpload - 0 then
update tbl
set Removed = t2.Removed
from tbl, tbl t2
where t2.RecipientID = tbl.RecipientID
and t2.Name = tbl.Name and t2.FreshUpload = 0
and tbl.FreshUpLoad = 1|||Hi,
We can use a cursor and instead of updating the "Removed" field, y cant we jus update the "FreshLoad" field with a 1 when the match is found.
Does this query help??
************************************************** ******
declare updaterec cursor
for
select * from <Tablename> where FreshUpload=1
open updaterec
fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed
while @.@.fetch_status=0
begin
Update <Tablename> set FreshLoad=1 where RecipientID=@.varRecipientID
fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed
end
close updaterec
deallocate updaterec
************************************************** *****
Please tell me if this helps.
Regards,
Ramya
Originally posted by eWW
I have been puzzling over this for nearly two days now and I'm about ready to dispair.
The task seems simple:
There are a few rows in a table that looks like this:
RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...
After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this
info@.test.com,Tester, 1, 07/18/2002
The closest I got to a solution was this:
CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),
AS
UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)
GO
This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.
Can anyone see how to copy selectable data from one row to another?
eWW|||Nigelrivett: I tried your solution first as it looked the leanest and BINGO!
That's it. You solved my riddle. Amazing how simple this can be when you know what you're doing.
Thank you very much nigelrivett!
and thank all of you who have helped.
eWW
[QUOTE][SIZE=1]Originally posted by nigelrivett
Not too clear what you want but something like this maybe
update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1