Combining the content of two tables

Hi all,

How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks

Table A

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Table B

Client Weight Purchase

Jim 2 5

Lee 4 3

Bob 6 7

Combination table (result)

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Jim 2 5

Lee 4 3

Bob 6 7

Give a look to the UNION and UNION ALL operators in books online. It should look something like this:

Code Snippet

select client,
weight,
purchase
from [table a]
union all -- or perhaps union
select client,
weight,
purchase
from [table b]

|||Works perfectly. Thanks.sqlsql

Combine SUMs

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery2

Since there is no group by, I will assume that you just working with one value, then you can simply do something like:

declare @.PDCs_IL int
select @.PDCs_IL = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

declare @.PDCs_IL_deleted int
select @.PDCs_IL_deleted = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

select @.PDCs_IL + @.PDCs_IL_deleted

or

select ( first query ) + ( second query )

as in

create table #test
(
value int
)
insert into #test values (1)
insert into #test values (1)
insert into #test values (1)

select ( select sum(value) from #test ) + ( select sum(value) from #test )

--note that you might need to check for a NULL value :)

You could probably just add another left join to the deleted table and include the where clause, but it is really messy to contemplate all that you are trying to do without more information. Either of these other methods should work fine if the two queries already work fine.

sqlsql

Combine Multiple Results into 1 RecordSet

Hello All
I have the following SPROC Below which I want to return the results of the 3
Querries in a single record Set which I can use in my webapp
/ ****************************************
****************
CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
AS
SELECT Count(*) As Total FROM vSQLInv_VersionString
SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
SELECT Count(*) As Valid FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
GO
****************************************
***************/
-- Desired Results --
Total Vulnerable Valid
80 5 75
Thanks
StuartSELECT Count(*) As Total,
SUM(CASE WHEN Status = 'Vulnerable' OR Status = 'EOF' THEN 1 ELSE 0 END)
As Vulnerable ,
SUM(CASE WHEN Status <> 'Vulnerable' THEN 1 ELSE 0 END) As Valid
FROM vSQLInv_VersionString
Jacco Schalkwijk
SQL Server MVP
"Stuart Shay" <sshay@.j51.com> wrote in message
news:umqEkOZMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello All
> I have the following SPROC Below which I want to return the results of the
> 3 Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>|||SELECT
(
SELECT Count(*) FROM vSQLInv_VersionString
) AS Total ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
) As Vulnerable ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
) As Valid
FROM
vSQLInv_VersionString
Cheers,
Greg Jackson
PDX, Oregon|||SELECT Count(*) As Total,
Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as Vulnerable,
Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
FROM vSQLInv_VersionString
"Stuart Shay" wrote:

> Hello All
> I have the following SPROC Below which I want to return the results of the
3
> Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>
>|||Thanks & Have A GREAT Day !!!!!
Stuart
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:41A33FEB-3805-411F-A03D-459849349C05@.microsoft.com...
> SELECT Count(*) As Total,
> Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as
> Vulnerable,
> Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
> FROM vSQLInv_VersionString
>
> "Stuart Shay" wrote:
>

column name alias concatenation

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.comWhats <BR>

Is this being done in SQL Server?|||Ideally I would like the column name/header to show something like 'Fri<BR>20'. The '<BR>' is just some web formating that is automatically incorporated.|||This should float your boat...

USE Northwind
GO

DECLARE @.cmd varchar(8000)

SELECT @.cmd = 'SELECT Quantity AS ['
+ CASE DATEPART(WeekDay,GetDate())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
+ '<BR>'
+ cast(Day(getDate()) as varchar(2))
+ '] FROM [Order Details]'

SELECT @.cmd

EXEC(@.cmd)|||Thanks, that was exactly what I was looking for.

Column Name

SQL 2K
I ran the below sql to list the table name and column_name with column_name
like 'ID'
select substring(so.name,1,50) 'Table Name',
substring(sc.name,1,50) 'Field Name'
from sysobjects so
inner join syscolumns sc on
so.id = sc.id
where so.type = 'U' and
sc.name like '%ID%'
order by so.name,sc.name
I want to include the rowcount for each table it returns with column ID = 100
Thanks In Advance
SmithUse information schema views instead.
select
table_name,
column_name
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
AMB
select table_name
"MS User" wrote:
> SQL 2K
> I ran the below sql to list the table name and column_name with column_name
> like 'ID'
> select substring(so.name,1,50) 'Table Name',
> substring(sc.name,1,50) 'Field Name'
> from sysobjects so
> inner join syscolumns sc on
> so.id = sc.id
> where so.type = 'U' and
> sc.name like '%ID%'
> order by so.name,sc.name
>
> I want to include the rowcount for each table it returns with column ID => 100
> Thanks In Advance
> Smith
>
>|||Thanks Mesa
I will change to use information schema, but then how to get the rowcount
for ID = 100 in all tables it returns
Thanks
Smith
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1907D283-6CEB-446A-9C51-CEEDC1967A69@.microsoft.com...
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>> SQL 2K
>> I ran the below sql to list the table name and column_name with
>> column_name
>> like 'ID'
>> select substring(so.name,1,50) 'Table Name',
>> substring(sc.name,1,50) 'Field Name'
>> from sysobjects so
>> inner join syscolumns sc on
>> so.id = sc.id
>> where so.type = 'U' and
>> sc.name like '%ID%'
>> order by so.name,sc.name
>>
>> I want to include the rowcount for each table it returns with column ID =>> 100
>> Thanks In Advance
>> Smith
>>|||Sorry, I did not read the message until the end. You have to use dynamic sql
in order to do this.
Example:
use northwind
go
create table #t (
tname sysname,
cname sysname,
rcnt int
)
declare @.tn sysname
declare @.cn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name),
quotename(column_name)
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
@.tn + N' where ' + @.cn + N' = ''100'''
print @.sql
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
AMB
"Alejandro Mesa" wrote:
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
> > SQL 2K
> >
> > I ran the below sql to list the table name and column_name with column_name
> > like 'ID'
> >
> > select substring(so.name,1,50) 'Table Name',
> > substring(sc.name,1,50) 'Field Name'
> > from sysobjects so
> > inner join syscolumns sc on
> > so.id = sc.id
> > where so.type = 'U' and
> > sc.name like '%ID%'
> > order by so.name,sc.name
> >
> >
> > I want to include the rowcount for each table it returns with column ID => > 100
> >
> > Thanks In Advance
> > Smith
> >
> >
> >|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...
> Sorry, I did not read the message until the end. You have to use dynamic
> sql
> in order to do this.
> Example:
> use northwind
> go
> create table #t (
> tname sysname,
> cname sysname,
> rcnt int
> )
> declare @.tn sysname
> declare @.cn sysname
> declare @.sql nvarchar(4000)
> declare my_cursor cursor local fast_forward
> for
> select
> quotename(table_schema) + '.' + quotename(table_name),
> quotename(column_name)
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.tn, @.cn
> if @.@.error != 0 or @.@.fetch_status != 0 break
> set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
> @.tn + N' where ' + @.cn + N' = ''100'''
> print @.sql
> insert into #t
> exec sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> AMB
>
>
> "Alejandro Mesa" wrote:
>> Use information schema views instead.
>>
>> select
>> table_name,
>> column_name
>> from
>> information_schema.columns
>> where
>> column_name like '%id%'
>> and objectproperty(object_id(quotename(table_schema) + '.' +
>> quotename(table_name)), 'IsUserTable') = 1
>> and objectproperty(object_id(quotename(table_schema) + '.' +
>> quotename(table_name)), 'IsMSShipped') = 0
>> order by
>> table_name,
>> ordinal_position;
>>
>> AMB
>>
>> select table_name
>> "MS User" wrote:
>> > SQL 2K
>> >
>> > I ran the below sql to list the table name and column_name with
>> > column_name
>> > like 'ID'
>> >
>> > select substring(so.name,1,50) 'Table Name',
>> > substring(sc.name,1,50) 'Field Name'
>> > from sysobjects so
>> > inner join syscolumns sc on
>> > so.id = sc.id
>> > where so.type = 'U' and
>> > sc.name like '%ID%'
>> > order by so.name,sc.name
>> >
>> >
>> > I want to include the rowcount for each table it returns with column ID
>> > =>> > 100
>> >
>> > Thanks In Advance
>> > Smith
>> >
>> >
>> >

Column Name

SQL 2K
I ran the below sql to list the table name and column_name with column_name
like 'ID'
select substring(so.name,1,50) 'Table Name',
substring(sc.name,1,50) 'Field Name'
from sysobjects so
inner join syscolumns sc on
so.id = sc.id
where so.type = 'U' and
sc.name like '%ID%'
order by so.name,sc.name
I want to include the rowcount for each table it returns with column ID =
100
Thanks In Advance
SmithUse information schema views instead.
select
table_name,
column_name
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
AMB
select table_name
"MS User" wrote:

> SQL 2K
> I ran the below sql to list the table name and column_name with column_nam
e
> like 'ID'
> select substring(so.name,1,50) 'Table Name',
> substring(sc.name,1,50) 'Field Name'
> from sysobjects so
> inner join syscolumns sc on
> so.id = sc.id
> where so.type = 'U' and
> sc.name like '%ID%'
> order by so.name,sc.name
>
> I want to include the rowcount for each table it returns with column ID =
> 100
> Thanks In Advance
> Smith
>
>|||Thanks Mesa
I will change to use information schema, but then how to get the rowcount
for ID = 100 in all tables it returns
Thanks
Smith
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1907D283-6CEB-446A-9C51-CEEDC1967A69@.microsoft.com...
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Sorry, I did not read the message until the end. You have to use dynamic sql
in order to do this.
Example:
use northwind
go
create table #t (
tname sysname,
cname sysname,
rcnt int
)
declare @.tn sysname
declare @.cn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name),
quotename(column_name)
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
@.tn + N' where ' + @.cn + N' = ''100'''
print @.sql
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
AMB
"Alejandro Mesa" wrote:
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...
> Sorry, I did not read the message until the end. You have to use dynamic
> sql
> in order to do this.
> Example:
> use northwind
> go
> create table #t (
> tname sysname,
> cname sysname,
> rcnt int
> )
> declare @.tn sysname
> declare @.cn sysname
> declare @.sql nvarchar(4000)
> declare my_cursor cursor local fast_forward
> for
> select
> quotename(table_schema) + '.' + quotename(table_name),
> quotename(column_name)
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.tn, @.cn
> if @.@.error != 0 or @.@.fetch_status != 0 break
> set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
> @.tn + N' where ' + @.cn + N' = ''100'''
> print @.sql
> insert into #t
> exec sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> AMB
>
>
> "Alejandro Mesa" wrote:
>

Column Name

SQL 2K
I ran the below sql to list the table name and column_name with column_name
like 'ID'
select substring(so.name,1,50) 'Table Name',
substring(sc.name,1,50) 'Field Name'
from sysobjects so
inner join syscolumns sc on
so.id = sc.id
where so.type = 'U' and
sc.name like '%ID%'
order by so.name,sc.name
I want to include the rowcount for each table it returns with column ID =
100
Thanks In Advance
Smith
Use information schema views instead.
select
table_name,
column_name
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
AMB
select table_name
"MS User" wrote:

> SQL 2K
> I ran the below sql to list the table name and column_name with column_name
> like 'ID'
> select substring(so.name,1,50) 'Table Name',
> substring(sc.name,1,50) 'Field Name'
> from sysobjects so
> inner join syscolumns sc on
> so.id = sc.id
> where so.type = 'U' and
> sc.name like '%ID%'
> order by so.name,sc.name
>
> I want to include the rowcount for each table it returns with column ID =
> 100
> Thanks In Advance
> Smith
>
>
|||Thanks Mesa
I will change to use information schema, but then how to get the rowcount
for ID = 100 in all tables it returns
Thanks
Smith
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1907D283-6CEB-446A-9C51-CEEDC1967A69@.microsoft.com...[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
|||Sorry, I did not read the message until the end. You have to use dynamic sql
in order to do this.
Example:
use northwind
go
create table #t (
tname sysname,
cname sysname,
rcnt int
)
declare @.tn sysname
declare @.cn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name),
quotename(column_name)
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
@.tn + N' where ' + @.cn + N' = ''100'''
print @.sql
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...[vbcol=seagreen]
> Sorry, I did not read the message until the end. You have to use dynamic
> sql
> in order to do this.
> Example:
> use northwind
> go
> create table #t (
> tname sysname,
> cname sysname,
> rcnt int
> )
> declare @.tn sysname
> declare @.cn sysname
> declare @.sql nvarchar(4000)
> declare my_cursor cursor local fast_forward
> for
> select
> quotename(table_schema) + '.' + quotename(table_name),
> quotename(column_name)
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.tn, @.cn
> if @.@.error != 0 or @.@.fetch_status != 0 break
> set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
> @.tn + N' where ' + @.cn + N' = ''100'''
> print @.sql
> insert into #t
> exec sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> AMB
>
>
> "Alejandro Mesa" wrote:

Column Name

SQL 2K
I ran the below sql to list the table name and column_name with column_name
like 'ID'
select substring(so.name,1,50) 'Table Name',
substring(sc.name,1,50) 'Field Name'
from sysobjects so
inner join syscolumns sc on
so.id = sc.id
where so.type = 'U' and
sc.name like '%ID%'
order by so.name,sc.name
I want to include the rowcount for each table it returns with column ID =
100
Thanks In Advance
SmithUse information schema views instead.
select
table_name,
column_name
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
AMB
select table_name
"MS User" wrote:

> SQL 2K
> I ran the below sql to list the table name and column_name with column_nam
e
> like 'ID'
> select substring(so.name,1,50) 'Table Name',
> substring(sc.name,1,50) 'Field Name'
> from sysobjects so
> inner join syscolumns sc on
> so.id = sc.id
> where so.type = 'U' and
> sc.name like '%ID%'
> order by so.name,sc.name
>
> I want to include the rowcount for each table it returns with column ID =
> 100
> Thanks In Advance
> Smith
>
>|||Thanks Mesa
I will change to use information schema, but then how to get the rowcount
for ID = 100 in all tables it returns
Thanks
Smith
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1907D283-6CEB-446A-9C51-CEEDC1967A69@.microsoft.com...[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Sorry, I did not read the message until the end. You have to use dynamic sql
in order to do this.
Example:
use northwind
go
create table #t (
tname sysname,
cname sysname,
rcnt int
)
declare @.tn sysname
declare @.cn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name),
quotename(column_name)
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
@.tn + N' where ' + @.cn + N' = ''100'''
print @.sql
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...[vbcol=seagreen]
> Sorry, I did not read the message until the end. You have to use dynamic
> sql
> in order to do this.
> Example:
> use northwind
> go
> create table #t (
> tname sysname,
> cname sysname,
> rcnt int
> )
> declare @.tn sysname
> declare @.cn sysname
> declare @.sql nvarchar(4000)
> declare my_cursor cursor local fast_forward
> for
> select
> quotename(table_schema) + '.' + quotename(table_name),
> quotename(column_name)
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.tn, @.cn
> if @.@.error != 0 or @.@.fetch_status != 0 break
> set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
> @.tn + N' where ' + @.cn + N' = ''100'''
> print @.sql
> insert into #t
> exec sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> AMB
>
>
> "Alejandro Mesa" wrote:
>

Column default value

Hi,
Anyone of you know how to change the default value for an existing column?
I have the script below but it might be a problem to run this script on
different server because the default constraint name might be different. Any
efficient way to solve this problem?
ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
GO
ALTER TABLE [dbo].[AcctSettings] ADD
CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
[ShowOrganizationWidePopup]
GO
Thanks,
KennyKenny
See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view to get the name of
contraint
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23DcH9j%23%23FHA.3676@.tk2msftngp13.phx.gbl...
> Hi,
> Anyone of you know how to change the default value for an existing column?
> I have the script below but it might be a problem to run this script on
> different server because the default constraint name might be different.
> Any efficient way to solve this problem?
> ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
> GO
> ALTER TABLE [dbo].[AcctSettings] ADD
> CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
> [ShowOrganizationWidePopup]
> GO
> Thanks,
> Kenny
>|||Before dropping an existing constraint you should know its name. If the name
was created automatically (if the constraint was "created by clicking, rathe
r
by typing" :)), you need to identify its name first either by looking at the
objects in QA or by inspecting the INFORMATION_SCEMA views.
ML
http://milambda.blogspot.com/|||Unfortunately, default names are not in the information_schema tables (at le
ast not in
CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a default is a column
attribute, not a
constraint.
So one would have to look up the name in sysobjects and syscolumns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
> Before dropping an existing constraint you should know its name. If the na
me
> was created automatically (if the constraint was "created by clicking, rat
her
> by typing" :)), you need to identify its name first either by looking at t
he
> objects in QA or by inspecting the INFORMATION_SCEMA views.
>
> ML
> --
> http://milambda.blogspot.com/|||Tibor

> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
Yes, you are right, how could I forget about it.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23svTrw%23%23FHA.4088@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
> So one would have to look up the name in sysobjects and syscolumns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
>

Column ''cb.CurrentBalance'' is invalid in the HAVING clause ...

Not sure why I am getting this error below. It has someting to do with my CurrentBalance calculation portion in my INNER JOIN area:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM, (SELECT (rb.RMSCHGAMT - rb.RMSRCVPCPL)

+(rb.RMSASSCCST - rb.RMSRCVDCST)

+(rb.RMSACRDINT - rb.RMSRCVDINT)

+(rb.UDCCOSTS1 - rb.UDCRECCS1)

+(rb.UDCCOSTS2 - rb.UDCRECCS2)

+(rb.RMSCOST1 - rb.RMSCOST1R)

+(rb.RMSCOST2 - rb.RMSCOST2R)

+(rb.RMSCOST3 - rb.RMSCOST3R)

+(rb.RMSCOST4 - rb.RMSCOST4R)

+(rb.RMSCOST5 - rb.RMSCOST5R)

+(rb.RMSCOST6 - rb.RMSCOST6R)

+(rb.RMSCOST7 - rb.RMSCOST7R)

+(rb.RMSCOST8 - rb.RMSCOST8R)

+(rb.RMSCOST9 - rb.RMSCOST9R)

+(rb.RMSCOST10 - rb.RMSCOST10R)

- rb.RMSXCSRCVS

FROM RPRDBAL rb) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, rf10.rmstranamt10

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)

+(RMSASSCCST - RMSRCVDCST)

+(RMSACRDINT - RMSRCVDINT)

+(UDCCOSTS1 - UDCRECCS1)

+(UDCCOSTS2 - UDCRECCS2)

+(RMSCOST1 - RMSCOST1R)

+(RMSCOST2 - RMSCOST2R)

+(RMSCOST3 - RMSCOST3R)

+(RMSCOST4 - RMSCOST4R)

+(RMSCOST5 - RMSCOST5R)

+(RMSCOST6 - RMSCOST6R)

+(RMSCOST7 - RMSCOST7R)

+(RMSCOST8 - RMSCOST8R)

+(RMSCOST9 - RMSCOST9R)

+(RMSCOST10 - RMSCOST10R)

- RMSXCSRCVS

) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

--WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, cb.CurrentBalance

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00

Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Collations Problem

I have recently migrated a SQL Server 6.5 DB to SQL 2000.

On a particular table i added a new varchar ( [field29] - see below)

now when changing a record in this table, the performance is greatly reduced.
In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :

"the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".

The table has 300,000 records. The update takes about 20secs.

After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.

Is this related to Collations?
([Field1] is the Primary Key)

any ideas?

CREATE TABLE [dbo].[Tabletest]
(
[field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field2] [smallint] NOT NULL ,
[field3] [datetime] NOT NULL ,
[field4] [datetime] NULL ,
[field5] [datetime] NULL ,
[field6] [datetime] NULL ,
[field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field12] [smallint] NOT NULL ,
[field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field15] [smallint] NULL ,
[field16] [smallint] NULL ,
[field17] [smallint] NULL ,
[field18] [smallint] NULL ,
[field19] [smallint] NULL ,
[field20] [smallint] NULL ,
[field21] [smallint] NULL ,
[field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field23] [smallint] NOT NULL ,
[field24] [bit] NOT NULL ,
[field25] [datetime] NULL ,
[field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field27] [int] NOT NULL ,
[field28] [smallint] NULL ,
[field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
)Use WHERE clause, you do not need to see 300,000 records when you are changing one :)