CREATE DATABASE failed: select

Showing posts with label select. Show all posts

Thursday, March 29, 2012

Create a view, failed

I tried to create a view with the next select, but the Enterprise Manager
send a message, "El Dise?ador de consultas no admite la interpretación SQL
de
CASE."
Not accept a CASE sql. I need your help to try other way. Thanks.
SELECT
'Raz_01'=
CASE
WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
(TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
ELSE 100
--Repeat this with other month 2,3,4-12--
END, CD_PAIS,COD_IND_LOC
From dbo.Datos_reales_2005
ORDER BY CD_PAIS, COD_IND_LOCUse SQL Query Analyzer instead.
AMB
"espinfire" wrote:

> I tried to create a view with the next select, but the Enterprise Manager
> send a message, "El Dise?ador de consultas no admite la interpretación S
QL de
> CASE."
> Not accept a CASE sql. I need your help to try other way. Thanks.
> SELECT
> 'Raz_01'=
> CASE
> WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
> (TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
> ((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-10
0
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
> ((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
> WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> ELSE 100
> --Repeat this with other month 2,3,4-12--
> END, CD_PAIS,COD_IND_LOC
> From dbo.Datos_reales_2005
> ORDER BY CD_PAIS, COD_IND_LOCsql

create a view of or select from a table on another DB

Does anyone know how you could create a view in one db of a table in another db on the same sql server? or how to perform a cross db join in a query?
thanks!Eg;
create view temp as
select a.* from
master..syslogins a, msdb..sysjobs

Use the books online feature that comes with SQL server. Most useful!
Meera

Tuesday, March 27, 2012

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a table using a Query

Hello,
With Oracle, we can create a table as following :
CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ...
With MSSQL, can we do the same thing ?The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list.

I suggest you to refer to books online for more information.|||Thanks a lot|||No mention, keep in touch with BOL:)

Create a table from select Queries.

Hi,

I wanted to know a query which will create a final result table from a combination of select queries.

The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.

null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.

Thanks in advance.select *
into resulttable
from (
select col1 as col1,
col2 as col2,
null as col3
from table1
union all
select null ,
col2 ,
null
from table2
union all
select null ,
null ,
col3
from table3
) as tm

Create a table from a select statement

Good afternoon.
Who knows how I can easily create a table where the column names come from another table and are created dynamically?

That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.

Theory would say:

Create table Bin
as select xyz from ABC
group by xyz

Unfortunately, MS Query Analyzer complains about the AS !!
SQL 2000 server.

Look forward to hearing about the correct way of doing this :-)Yep, SQL Server doesn't like the Oracle format for doing this.

Try

Select XYZ
Into Bin
From ABC
Group by xyz

This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.

Hope this helps.|||Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
This enters the data as rows. I need columns so that I can add the "real" data afterwards.
Any more ideas ?
Thanks very much|||Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.

Run this statement:

select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')

If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.

blindman|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_1r94.asp

Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.|||Interesting, very interesting.
I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
What a nightmare, I hate work arounds, but it is a once off DB create.
Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
Take care and enjoy
HandyMac|||Option No 2
Open Fox 7, work with data, create the required table, import into SQL server.
Life is great :-)
Take care and have a great weekend,
HandyMacsql

Sunday, March 25, 2012

Create a new date

Hi,

I want to write a query that returns me the first date of the month...

I wrote this query

SELECT DateAdd(day,- Day(GetDate()) + 1,GetDate())

THis works fine for me, is there any function that build a new date, without using the DateAdd function?

here is another way

select dateadd(mm, datediff(mm, 0, getdate())+0, 0)

and another

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks

I used the second query :

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

Sunday, March 11, 2012

CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>

Saturday, February 25, 2012

CPU and Memory counters

Hi,
Id like to collect some information about SQL server itself like "% of CPU
Usage" or "% of memory usage".
Something like SELECT @.@.CPU_Usage ...... ?
Thanks,
Tim.Use Performance Monitor. You can set it up to record automagically on
specific schedules/durations/frequencies as well as choosing your
objects/counters.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Id like to collect some information about SQL server itself like "% of CPU
> Usage" or "% of memory usage".
> Something like SELECT @.@.CPU_Usage ...... ?
> Thanks,
> Tim.
>|||Thank you, Geoff,
problem is - I need this counter into me SELECT statement to put it in a
table.
Any idea?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Use Performance Monitor. You can set it up to record automagically on
> specific schedules/durations/frequencies as well as choosing your
> objects/counters.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Tim Xox" <timur@.mtgsi.com> wrote in message
> news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > Id like to collect some information about SQL server itself like "% of
CPU
> > Usage" or "% of memory usage".
> > Something like SELECT @.@.CPU_Usage ...... ?
> > Thanks,
> > Tim.
> >
> >
>|||Take a look at the sysperfinfo table.
--
Andrew J. Kelly
SQL Server MVP
"Tim Xox" <timur@.mtgsi.com> wrote in message
news:uZfayn9oDHA.488@.tk2msftngp13.phx.gbl...
> Thank you, Geoff,
> problem is - I need this counter into me SELECT statement to put it in a
> table.
> Any idea?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uufC%23T9oDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Use Performance Monitor. You can set it up to record automagically on
> > specific schedules/durations/frequencies as well as choosing your
> > objects/counters.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> > "Tim Xox" <timur@.mtgsi.com> wrote in message
> > news:uMl%23e98oDHA.1708@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > Id like to collect some information about SQL server itself like "% of
> CPU
> > > Usage" or "% of memory usage".
> > > Something like SELECT @.@.CPU_Usage ...... ?
> > > Thanks,
> > > Tim.
> > >
> > >
> >
> >
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:
> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||artunc,
lose the parentheses around your column list in your select statement
-st|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...
The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:

> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
|||artunc,
lose the parentheses around your column list in your select statement
-st
|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>
|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Friday, February 24, 2012

coverting to date problem

SELECT DISTINCT CAST(YEAR(classdate) AS varchar(4)) + '/' + CAST(MONTH(classdate) AS varchar(2)) as ok ,{fn MONTH(dbo.classgiven.classdate)} as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) order by 1", conNorthwind)

hello how do convert "CAST(YEAR(classdate) AS varchar(4)) + '/1/' + CAST(MONTH(classdate) AS varchar(2)) as ok" to a date in my sql statement?

so that it comes out as MM/DD/YYYY

thank you all

agian "OK" has to come out as a date..thanks

you can use

convert(varchar(20),classdate,101)

in you query, this will converts the date in the MM/dd/yyyy format.

|||

It looks as thoughclassdate is a date type anyway so, rather than create unnecessary function calls in your SQL statement, simply return the date and format it in whatever control you are using to display the data.

Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name, 'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
--
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>

Sunday, February 19, 2012

counting values in aresult set

Hi,
I have the following query:
SELECT ahd.call_req.ref_num AS Incident
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
which returns a single column called Incidents containing integer values.
4323
4316
4316
4309
4309
4309
What I want is a list of the Integer values and the coresponding number of
times
it appears in the result set.For example;
4323 1
4316 2
4309 3
--
roryI forot to say thank you in advance for any help!
Best wishes
--
rory
"Rory" wrote:
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||this is untested but try
SELECT ahd.call_req.ref_num AS Incident, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by Incident
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rory" <rory@.discussions.microsoft.com> wrote in message
news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number
> of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||Hi Hilary,
Thanks for that, unortunately it does not work.
Regards,
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
Thanks for your reply, unfortunately it doesn't work. Back to
the "drawing board" for me I'm afraid!
Thanks again
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
I modified the query slightly from your suggestion( just column
names) and hey presto! Success!
Thanks you've made it a very nice Friday for me! Thanks again.
SELECT ahd.call_req.ref_num, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Rory,
Change the "group by" clause and use the correct column.
SELECT
ahd.call_req.ref_num AS Incident, count(*)
FROM
ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
go
AMB
"Rory" wrote:
> Hi Hilary,
> Thanks for your reply, unfortunately it doesn't work. Back to
> the "drawing board" for me I'm afraid!
> Thanks again
> --
> rory
>
> "Hilary Cotter" wrote:
> > this is untested but try
> >
> > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> > group by Incident
> >
> >
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > Hi,
> > > I have the following query:
> > > SELECT ahd.call_req.ref_num AS Incident
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > >
> > > which returns a single column called Incidents containing integer values.
> > > 4323
> > > 4316
> > > 4316
> > > 4309
> > > 4309
> > > 4309
> > > What I want is a list of the Integer values and the coresponding number
> > > of
> > > times
> > > it appears in the result set.For example;
> > >
> > > 4323 1
> > > 4316 2
> > > 4309 3
> > > --
> > > rory
> >
> >
> >|||Hi Alejandro,
I modified the query and it works fine,
SELECT ahd.call_req.ref_num, count(*) AS Hops
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
thanks for your response it is very much appreciated.
--
rory
"Alejandro Mesa" wrote:
> Rory,
> Change the "group by" clause and use the correct column.
> SELECT
> ahd.call_req.ref_num AS Incident, count(*)
> FROM
> ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by ahd.call_req.ref_num
> go
>
> AMB
>
> "Rory" wrote:
> > Hi Hilary,
> > Thanks for your reply, unfortunately it doesn't work. Back to
> > the "drawing board" for me I'm afraid!
> > Thanks again
> > --
> > rory
> >
> >
> > "Hilary Cotter" wrote:
> >
> > > this is untested but try
> > >
> > > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > > group by Incident
> > >
> > >
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > > Hi,
> > > > I have the following query:
> > > > SELECT ahd.call_req.ref_num AS Incident
> > > > FROM ahd.call_req JOIN ahd.act_log
> > > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > > WHERE ahd.act_log.type = 'TR'
> > > >
> > > > which returns a single column called Incidents containing integer values.
> > > > 4323
> > > > 4316
> > > > 4316
> > > > 4309
> > > > 4309
> > > > 4309
> > > > What I want is a list of the Integer values and the coresponding number
> > > > of
> > > > times
> > > > it appears in the result set.For example;
> > > >
> > > > 4323 1
> > > > 4316 2
> > > > 4309 3
> > > > --
> > > > rory
> > >
> > >
> > >

counting total rows when using GROUP BY

hi,

i have a stored procedure

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsWHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)GROUP BY UserNameUNIONSELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsAS UserVisits_1WHERE (ProductID = @.ProductID)AND (UserNameISNULL)GROUP BY AnonimIP

this will return something like:

zuperboy90 - 4 visits

ANONIMOUS - 6 visits

85.104.103 - 2 visits etc

how can i count the rows returned in both selections (4+6+2 = 12) ?

thank you

Put your whole query as a sub query then get the sum of TotalVisits.(4+6+12) as it is in your example

Select SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP

) x

|||

hi.

thanks for code, but if i write this i'll lost the other columns "TotalVisit" and "Visitor" :(

|||

Try this:

SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisits

WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)

GROUPBY UserName

UNION ALL

SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_1

WHERE(ProductID= @.ProductID)AND(UserNameISNULL)

GROUPBY AnonimIP

SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit

FROM UserVisitsAS UserVisits_2

WHERE(ProductID= @.ProductID)

|||

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

) x

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP

) x

|||

david wendelken:
Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP
) x

i get this error:Column 'x.Visitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

david wendelken:
Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP
) x

i get this error:Each GROUP BY expression must contain at least one column that is not outer reference

i am sorry i don't know sql at all:(

|||

jogi:
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)
GROUPBY UserName
UNION ALL
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE(ProductID= @.ProductID)AND(UserNameISNULL)
GROUPBY AnonimIP
SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_2
WHERE(ProductID= @.ProductID)

i get this warrning:Error in GROUP BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.

|||Sorry, it appears that I didn't make a full copy of the original statement.

Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

) x

GROUP BY Visitor

Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.

You might want to try this instead:

Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM

(

) x

Group by Visitor

|||

hi, thanks for code

that stored procedure returns exacly what is in the SELECT clause: :(

127.0.0.124zuperboy903Test1@.yahoo.com1test2@.yahoo.com2

to simplify the problem, i will make a separate stored procedure for storing the total comments...i don't thnik is big deal another comand to database

thanks

|||

Glad that worked for you! Be sure to mark which answer(s) worked, so others will know too!

There is a general lesson to learn here about sql: think in sets of data, not rows of data.

By putting parentheses around our select statement, we created a set of data.

Then we queried from that set.

If you can properly define the sets of data that you need, the sql often becomes extremely simple.

Counting the number of fields that are populated in row

I have 2 large tables that I'm selecting off of. part of my select is
to get the number of stores visited. The table that has this
information has the store names as columns. If a store wasn't visited
there will be a null value. I need to be able to count the number of
fields that don't have the null value in order to get the desired
result. I can't use the Count() function as the select statement is
very large. and I don't want to group by all of the fields that I'm
selecting.
Does anyone know of another way to get this?what data type are the store name columns?
numeric (of some sort) indicating number of visits?
bit indicating visited?
are you returning the store columns as well?
dmagoo22 wrote:
> I have 2 large tables that I'm selecting off of. part of my select is
> to get the number of stores visited. The table that has this
> information has the store names as columns. If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result. I can't use the Count() function as the select statement is
> very large. and I don't want to group by all of the fields that I'm
> selecting.
>
> Does anyone know of another way to get this?
>|||> to get the number of stores visited. The table that has this
> information has the store names as columns.
Egads. So if you add a store, you change your schema? That's really not
how it should work. The store names are *data* not *metadata*...

> If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result.
If this returns exactly one row, you can try this:
SELECT
col1,
col2,
NumberOfStoresVisited =
CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
+ CASE WHEN Store2 = NULL THEN 0 ELSE 1 END
+ ...
+ CASE WHEN StoreN = NULL THEN 0 ELSE 1 END
FROM
table
...
Without better specs, that's about as good as I can do. Please see
http://www.aspfaq.com/5006|||> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
WHOA! I meant WHEN Store1 IS NULL
*smack*|||try the @.@.rowcount Function
run the select statement to select where your column is not null and then
get the @.@.rowcount
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHOA! I meant WHEN Store1 IS NULL
> *smack*
>
>|||> try the @.@.rowcount Function
> run the select statement to select where your column is not null and then
> get the @.@.rowcount
Unfortunately, I think the OP's table looks like this:
Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1
So @.@.ROWCOUNT will always be 1...|||Oops!!!! Sorry thot the data was in multiple rows
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unfortunately, I think the OP's table looks like this:
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
> So @.@.ROWCOUNT will always be 1...
>
>

Friday, February 17, 2012

Counting result

Hi Champs!
I have a tricky sql problem, and I would apreciate some help from you.
In my SELECT statement my result looks like this:
ID--name--city--number
1 -- lfkjdfl--djdjjd--1212
2-- mdsf-- safta--1212
3--jsert--agjyt--1212
4--wqfh--jfgyiu--3434
5--aghj--jqqre--3434
But now I want a added column, in my SELECT result, so it would look like:
ID--name--city--number--newColumn
1 -- lfkjdfl--djdjjd--1212--1
2-- mdsf-- safta--1212--2
3--jsert--agjyt--1212--3
4--wqfh--jfgyiu--3434--1
5--aghj--jqqre--3434--2
How could I produce the "newColumn" ?
Thanks
KurlanSELECT A.id, A.name, A.city, A.number, COUNT(*)
FROM YourTable AS A
JOIN YourTable AS B
ON A.number = B.number
AND A.id >= B.id
GROUP BY A.id, A.name, A.city, A.number
David Portas
SQL Server MVP
--|||There are several ways.. One which uses a correlated subquery is as follows
:
Select Id, Name,City, Number,
(Select Count(*) From Table
Where number = T.Number
And Id <= T.Id)
From Table T
Order By Id
Other approaches use either a Temp Table, or a table variable, or a Created
View to hold the Sount values, and then join that back to the main table to
output the resutls...
"Kutlan" wrote:

> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||Thank You!!! this really saved my day
"CBretana" wrote:
> There are several ways.. One which uses a correlated subquery is as follo
ws:
> Select Id, Name,City, Number,
> (Select Count(*) From Table
> Where number = T.Number
> And Id <= T.Id)
> From Table T
> Order By Id
>
> Other approaches use either a Temp Table, or a table variable, or a Create
d
> View to hold the Sount values, and then join that back to the main table t
o
> output the resutls...
>
>
> "Kutlan" wrote:
>|||Thank You!!! this really saved my day
"David Portas" wrote:

> SELECT A.id, A.name, A.city, A.number, COUNT(*)
> FROM YourTable AS A
> JOIN YourTable AS B
> ON A.number = B.number
> AND A.id >= B.id
> GROUP BY A.id, A.name, A.city, A.number
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yr welcome !
"Kutlan" wrote:
> Thank You!!! this really saved my day
>
> "CBretana" wrote:
>|||Not that it helps you now, but this would be so much simpler and more
efficient in SQL Server 2005:
SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
FROM T1
:-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:C86612FB-17E9-4B54-BD03-CCCC113FE8A0@.microsoft.com...
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||It would be much simpler with the RAC utility for S2k NOW:).
Sql99 ranking functions row_number,rank and dense_rank with
partitioning/ordering
have been in available in RAC for years!...and some additional functions MS
obviously
hasen't thought about :)...scary:)
www.rac4sql.net
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23G6iw5RWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Not that it helps you now, but this would be so much simpler and more
> efficient in SQL Server 2005:
> SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
> FROM T1
> :-)

counting records in all tables

Hello all,

I was wondering if there is anyway to find number of records in all tables instead of count one table at a time by select count(*) from table_name.

thanks

The system tables are good for this. They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

I use the following to get the row count, data bytes, and index bytes for all tables:

SELECT

sys.schemas.[name] AS [Schema],

sys.tables.name AS [Table],

COALESCE([Row Count].[Count], 0) AS [Rows],

COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

FROM sys.tables

INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

LEFT OUTER JOIN (

SELECT

object_id,

SUM(rows) AS [Count]

FROM sys.partitions

WHERE index_id < 2

GROUP BY object_id

) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

ORDER BY sys.tables.[name]

(The weird text editor on this site messed up the formatting of the code; I apologize)

-Ryan

|||

Hello Ryan and all,

I am using SQL Server 2000; therefore, I won't be able to see table as sys.schemas and sys.tables. Would you advise how to do this in SQL Server 2000 ?

thanks

|||You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

Just wondering: why are you interested in this information?

Thanks,|||

hello Marcel and all,

I know there will be no guarantee unless using select(*). However, estimate number would be fine. Because I am looking for large table to tune.

|||

Unfortunately, we're fully upgraded to SQL 2005 here (we were active in the beta and were ready to go when 2005 was released). So I can't accurately convert it back to 2000 code.

While SQL 2000 doesn't have a sys.tables, it does have a systables. It should be possible to get this working on the older version with minor adjustments.

-Ryan

|||This is a quick and dirty procedure that uses the results from

sp_spaceused to create a simple report. I've used it to weed out a few

space hogs on a 2000 server. Note that getting accurate row counts is

probably contingent upon having your usage statistics up to date (see

DBCC UPDATEUSAGE). Create the procedure in master, and run it from

within the database you want to profile, like other sp_ procedures.

Tweak it to suit your needs - I'm sure there's room for improvement.

CREATE PROCEDURE sp_tablesizes @.biggestfirst bit = 0
AS
-- sp_tablesizes
-- Dave Britten, 2007
-- Produces a list of all user and system tables in the current database,
-- optionally sorted by physical size. Also returns grand totals in a
-- second result set.

SET NOCOUNT ON
CREATE TABLE #tablelist (
Owner sysname,
Name sysname
)

CREATE TABLE #tabledata (
Name varchar(128),
Rows char(11),
reserved varchar(18),
Data varchar(18),
index_size varchar(18),
Unused varchar(18)
)

CREATE TABLE #sizedata (
rows int,
reserved int,
Data int,
index_size int,
Unused int
)

DECLARE @.dbname sysname
SET @.dbname = db_name()
DECLARE @.tablename sysname
DECLARE @.ownername sysname
DECLARE @.tablestring varchar(261)

INSERT INTO #tablelist
EXEC('SELECT sysusers.name, sysobjects.name FROM [' + @.dbname + ']..sysusers INNER JOIN [' + @.dbname + ']..sysobjects ON sysobjects.uid = sysusers.uid WHERE type IN (''U'', ''S'') AND sysobjects.name NOT LIKE ''tempdb..%''')

DECLARE tables CURSOR FAST_FORWARD FOR
SELECT Owner, Name FROM #tablelist
OPEN tables

FETCH NEXT FROM tables INTO @.ownername, @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tablestring = '[' + @.ownername + '].[' + @.tablename + ']'
INSERT INTO #tabledata
EXEC sp_spaceused @.tablestring
FETCH NEXT FROM tables INTO @.ownername, @.tablename
END

CLOSE tables
DEALLOCATE tables

INSERT INTO #sizedata (rows, reserved, data, index_size, unused)
SELECT
rows,
CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int),
CAST(LEFT(data, PATINDEX('% KB', data)) AS int),
CAST(LEFT(index_size, PATINDEX('% KB', index_size)) AS int),
CAST(LEFT(unused, PATINDEX('% KB', unused)) AS int)
FROM #tabledata

IF @.biggestfirst = 1
SELECT * FROM #tabledata ORDER BY CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int) DESC
ELSE
SELECT * FROM #tabledata ORDER BY name ASC

SELECT
SUM(rows) [Total Rows],
STR(SUM(reserved)) + ' KB' [Total reserved],
STR(SUM(data)) + ' KB' [Total Data],
STR(SUM(index_size)) + ' KB' [Total index_size],
STR(SUM(unused)) + ' KB' [Total Unused]
FROM #sizedata

DROP TABLE #tablelist
DROP TABLE #tabledata
DROP TABLE #sizedata

GO|||

Hello davidbrit2 and all,

Thank you davidbrit2 for the replied. When I complied the stored procedure, I got the problem at database name as a variable.

e.g: DECLARE @.dbName AS VARCHAR(30)

SELECT @.dbName = 'Another Database'

SELECT name FROM [' + @.dbName + ']..sysobjects WHERE type = 'U' ORDER BY name

Then I received error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '' + @.dbName + '..sysobjects'.

Please advise.

|||

For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

SELECT OBJECT_NAME(si.[id]), si.[rows]

FROM dbo.sysindexes si

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0
AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want to include the tables' owners then you could use this:

SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

FROM dbo.sysindexes si

INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]
INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0
AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want accurate row counts and can't be bothered creating and using a cursor then use this:

CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, TableRowCount

FROM #tmpOutput

ORDER BY 1

DROP TABLE #tmpOutput

Chris

|||

Here is the script that can give you the number of counts in each table -

Declare @.testSql varchar(300)
Create Table #TempTabCount(TABLE_QUALIFIER sysname NULL,
TABLE_OWNER sysname NULL,
TABLE_NAME sysname NULL,
NON_UNIQUE smallint NULL,
INDEX_QUALIFIER sysname NULL,
INDEX_NAME sysname NULL,
TYPE smallint NULL,
SEQ_IN_INDEX smallint NULL,
COLUMN_NAME sysname NULL,
COLLATION char(1) NULL,
CARDINALITY int NULL,
PAGES int NULL,
FILTER_CONDITION varchar(128) NULL)

Declare TAB_CURSOR CURSOR for
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES

Open TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @.testSql
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTabCount(TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION)
exec sp_statistics @.testSql
FETCH NEXT FROM TAB_CURSOR INTO @.testSql
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
Select TABLE_OWNER, TABLE_NAME, CARDINALITY from #TempTabCount

See if it solves your purpose. I tested it in SQL 2000 and working for me.

Enjoy!

Ash

|||Chris,

Ah-ha, it's sysindexes. I knew they had to be storing that information SOMEWHERE. ;)|||

Hi All,

Please find the query which will help to find the total number of records in all the tables in a SQL Server Database.

Select substring(obj.name, 1, 50) as Table_Name,
ind.rows as Number_of_Rows
from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id
where
obj.xtype = 'u'and ind.indid < 2
order by
obj.name

Regards
AKMEHTA