Correlated Subqueries?

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?
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?

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
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:
>

Correct way of finding a tables primary keys?

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.

I thought that this code did the trick.

***

DECLARE @.c varchar(4000), @.t varchar(128)
SET @.c = ''
SET @.t='contact_pmc_contact_relations'
Select @.c = @.c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @.t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@.c, 1, Datalength(@.c) - 1)

***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.

Here is a dump from one of the tabels where it doesn't work.

SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table

***

id indid colid keyno

933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.

If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards

/Anders

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

|||

SQL Server 2000 ou 2005 ?

|||Currently we'r using sql server 2000, but if there is a way that works on both 2000 and 2005 it would be really great!|||

Madhu K Nair wrote:

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

Either im missunderstanding or that doesn't work. I want to get the columns that compose the primarykey. Not the primary key itself.

|||

i got the issue. Since there is no direct method (Single select statement), I will give u a workaround. If u check sp_helpconstraint , it give u the details. U can use sp_helptext to find the sourcecode behind this SP and do the required modification adn create u r own SP.

Madhu

|||

select * from INFORMATION_SCHEMA.[CONSTRAINT_COLUMN_USAGE]

where constraint_type='PRIMARY KEY'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is this you are looking for?

Code Snippet

SELECT CCU.TABLE_NAME

, CCU.COLUMN_NAME

, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON TC.TABLE_NAME = CCU.TABLE_NAME

AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY CCU.TABLE_NAME, ORDINAL_POSITION

Just expanding to what others meant to answer to your question.

Correct Procedures for Testing Against NULLs from SQL Server

Hi all,
I have some C# code that is pulling data from a database where a majority of the values being retrieved areNULL, yet their initial column data types are bothstringandint, which means that I have to temporarily store theseNULL's inintandstringdatatypes in C#. Later on in my code I have to test against these values,and was wondering if I am doing it correctly with the following code.
The following statement the variableor_team_home_idis of astringdata type, but may have had aNULLvalue assigned to it from the database
if (!or_team_home_id.Equals(DBNull.Value)) {}
The following statement the variableor_manager_id is of aintdata type, but also may have aNULLvalue assigned to it from the database.
if (!Convert.IsDBNull(or_manager_id)){}
Are these the correct way to test against NULL values retrieved fromteh database and that are stored in their respective data types.
Tryst
For string types you could compare with System.DbNull.Value as in (VB.NET)
If strVal IS System.DbNull.Value then

End If
For numeric datatypes I'd recommend doing an ISNULL(column,0) from the SQL stmt so it will be easier/faster to check for 0 from the front end.sql

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key value
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following ?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LW
I think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
David Portas
SQL Server MVP
|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:

> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>
|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
David Portas
SQL Server MVP
|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:

> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your table.
> 2. The SELECT statement will select ALL rows from Table1 unless you specify
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key value
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following ?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LWI think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
--
David Portas
SQL Server MVP
--|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:
> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
--
David Portas
SQL Server MVP
--|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:
> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your table.
> 2. The SELECT statement will select ALL rows from Table1 unless you specify
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key valu
e
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following
?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LWI think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
David Portas
SQL Server MVP
--|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:

> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
David Portas
SQL Server MVP
--|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:

> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your tabl
e.
> 2. The SELECT statement will select ALL rows from Table1 unless you specif
y
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows
,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Copying one text column to another

Hi All
I have to copy text data from a table in one database to a table in a
different database, both table reside on the same server.
I tried two options which didn't work (see code below), and will be more
than gratefull for any help.
Thanks
Elie Grouchko
***************************************
I tried the following (didn't work):
UPDATE database2.dbo.table2
SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
tablekey1 = @.tablekey1)
WHERE tablekey2 = @.tablekey2
***************************************
I also tried (also didn't work):
Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
WHERE tablekey1 = @.tablekey1
Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
WHERE tablekey2 = @.tablekey2
UpdateText
database2.dbo.table2.textcolumn2 @.textcolumn2ptr
Null
0
database1.dbo.table1.textcolumn1 @.textcolumn1ptrElie,
I think this will do what you want:
update database2.dbo.table2 set
textcolumn2 = T1.textcolumn1
from database1.dbo.table1 as T1
where T1.tablekey1 = database2.dbo.table2.tablekey2
and database2.dbo.table2.tablekey2 = @.tablekey2
Steve Kass
Drew University
Elie Grouchko wrote:

>Hi All
>I have to copy text data from a table in one database to a table in a
>different database, both table reside on the same server.
>I tried two options which didn't work (see code below), and will be more
>than gratefull for any help.
>Thanks
>Elie Grouchko
>***************************************
>I tried the following (didn't work):
> UPDATE database2.dbo.table2
> SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
>tablekey1 = @.tablekey1)
> WHERE tablekey2 = @.tablekey2
>***************************************
>I also tried (also didn't work):
> Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
> WHERE tablekey1 = @.tablekey1
> Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
> WHERE tablekey2 = @.tablekey2
> UpdateText
> database2.dbo.table2.textcolumn2 @.textcolumn2ptr
> Null
> 0
> database1.dbo.table1.textcolumn1 @.textcolumn1ptr
>
>|||Great, it works
Thanks
:)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23V4SREbIFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Elie,
> I think this will do what you want:
> update database2.dbo.table2 set
> textcolumn2 = T1.textcolumn1
> from database1.dbo.table1 as T1
> where T1.tablekey1 = database2.dbo.table2.tablekey2
> and database2.dbo.table2.tablekey2 = @.tablekey2
> Steve Kass
> Drew University
> Elie Grouchko wrote:
>sql

Copying Indexes from one table to another table programatically

Programatically in a stored proc, Is there anyway to copy/create all indexes
from one table to another table provided both tables have same column
deifinition? Please let me know.On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all indexes
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How can I script the indexes within the proc?
"David Portas" wrote:
> On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> > Programatically in a stored proc, Is there anyway to copy/create all indexes
> > from one table to another table provided both tables have same column
> > deifinition? Please let me know.
> Why can't you use CREATE INDEX? Script the indexes when you create the
> proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.
David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison
|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>
|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison
|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to
your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like
what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does t
hem all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE
Then from in there you can load a table, execute a query, etc
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

Copying from one column to another

It's all in the same table
How do I copy something from one column to another, but limiting it to only the first "x" characters?

so if i wanted to copy just 'ABC' instead of 'ABCDEFG' how could i do that?

thanks in advanceSomething like:

DECLARE @.x int; SET @.x = 3
UPDATE <table>
SET COL2 = LEFT(COL1,@.x)

|||Thank you :}

Can I ask what LEFT means?|||LEFT Returns the part of a character string starting at a specified number of characters from the left. In this case the left 3 characters.

Have a look into SQL Server Books Online.|||ooh so you could even do right?
and what about center?

i'll do that. you have any good sites offhand? i have a few sites but none seem to be "that great"|||Regarding the center or any other portion of a string that doesn't start at the left end or right end you would use the SUBSTRING function. In terms of sites for Tranact SQL I don't knwo of to many and have found the SQL Server Books Online reference to be pretty complete.

Cheers

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.
Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.UJ wrote:
> I am copy a database from one server to another and when I do, I lose all of
> my identity column specifications. The column is still there, but it's no
> longer an identity column.
> Am I missing something?
> TIA - Jeff.
>
How are you performing the copy?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
>> I am copy a database from one server to another and when I do, I lose all
>> of my identity column specifications. The column is still there, but it's
>> no longer an identity column.
>> Am I missing something?
>> TIA - Jeff.
>>
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||UJ wrote:
> Using the Copy Database Wizard.
>
Ok... Hard to tell what that thing does... You'd be better off using
one of the more accepted methods:
- backup/restore
- detach/attach
Either method will guarantee that you get an exact copy of the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying column into another

Hi,
We already have "Data Entry Time" column with hundreds of records in it, and
realized later we also need "Data Update Time." The "Data Update Time"
should not be NULL. So as a starting point, we will copy everything in the
"Data Entry Time" column into the "Data Update Time" column for the already
existing records, and then each will go its own way. So my question is how
do I copy the "Data Entry Time" column with records into the "Data Update
Time" column?
YCAsp Psa wrote:
> Hi,
> We already have "Data Entry Time" column with hundreds of records in it, a
nd
> realized later we also need "Data Update Time." The "Data Update Time"
> should not be NULL. So as a starting point, we will copy everything in th
e
> "Data Entry Time" column into the "Data Update Time" column for the alread
y
> existing records, and then each will go its own way. So my question is ho
w
> do I copy the "Data Entry Time" column with records into the "Data Update
> Time" column?
> YC
UPDATE your_table
SET data_update_time = data_entry_time
WHERE data_update_time IS NULL ;
ALTER TABLE your_table
ALTER COLUMN data_update_time DATETIME NOT NULL;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you!
YC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141860909.277930.39530@.z34g2000cwc.googlegroups.com...
> Asp Psa wrote:
> UPDATE your_table
> SET data_update_time = data_entry_time
> WHERE data_update_time IS NULL ;
> ALTER TABLE your_table
> ALTER COLUMN data_update_time DATETIME NOT NULL;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

copying all rows from one table into another existing table and overwriting data

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

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 row to new table - not copying one date column

I have two tables, a PendingOrder table and a CompletedOrder table. I have a stored procedure that moves an order from the pending table to the completed table, and I am having an issue with copying over a date field (the date the order was created). Here is what I have....

The store procedure takes in the following:
@.PendOrderKey INT,
@.Status INT

Here is my insert statement:

INSERT INTO CustOrder (CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, Status, OrderTypeKey, CreatedDate, ModifiedDate)
SELECT CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, @.Status, OrderTypeKey, CreatedDate, GETDATE()
FROM PendCustOrder
WHERE PendOrderKey = @.PendOrderKey

For some reason, when I run this on an order, instead of taking the value of "CreatedDate" from the pending order table and inserting it into the CreatedDate field of the completed order table, it inserts the current date and time into the completed order table. The "CreatedDate" field in the completed orders table does not allow nulls and has no default value, so I'm confused as to why it's inserting the current date/time. Any help would be greatly appreciated. Thanks!!

Maguidhir:

Check and see if the target table has a trigger that sets the "CreatedDate" field when a new record is inserted into the table.

Dave

|||Thank you so much! It does have a trigger that sets the CreatedDate=GetDate()!

Copying a column to a SSIS package variable

I need to use a value retrieved in one data flow in the second data flow. What's the best way to do this?

How do I copy the column retrieved to a variable so I can use that variable in the second data flow?

Data Flow implies multiple rows, which doesn't naturally fit with a single variable.

Normally I would be using an Exec SQL Task to populate a variable from a table. You could use a Script Component in the data flow, or perhaps the Recordset Destination if there are several rows.

If reading just one value/line from a file for example, then I'd just use the Script Task.