Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Friday, March 30, 2012

How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:


SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
Field2 LIKE '%NEW%' OR
Field3 LIKE '%NEW%' OR
Field4 LIKE '%NEW%'

Regards,

Wednesday, March 28, 2012

How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

Friday, March 9, 2012

How to concatenate two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatenate two text fields?
Thanks
Raju
maybe you can export the 2 columns into excel
eg. first column is in cell A1, second column is in cell B1
at C1 you type this formula =A1&B1, then copy this formula till the end of
the row, then you import it back to your table.
Susanna
"Raju" wrote:

> Hi all,
>
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 + text
> column 2.
>
> Is there an easy way to concatenate two text fields?
>
> Thanks
> Raju
>
>
|||If you created a third column in your table you use an UPDATE statement
to do this like:
UPDATE Sometable
SET col3 = ISNULL(col1,'') + ISNULL(col2,'')
or in a view you could use the almost same syntax like:
SELECT ISNULL(col1,'') + ISNULL(col2,'') as col3
THE ISNULL(col1,'') syntax is related to the issue that in some cases
the attributes could be NULL rather than just an empty or regular
string and this would lead to a NULL result concatenating the two
values together.
HTH, Jens Suessmeyer.

how to concatenate the xml returned in different rows, and different select statements

hi
How would I write my query to concatenate the xml segment returned from
different rows, and from different select statements
Look at FOR XML...
E.g.,
declare @.x1 xml, @.x2 xml;
set @.x1 = '<a/>';
set @.x2 = '<b/>';
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>
|||I would recommend you go with FOR XML, as Michael Rys suggested. However, if for
some reason you're unable or not willing to use that method, you can always
convert your instances to a string type, concatenate them, and convert back to
XML
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>

How to concatenate multiple rows into one field?

Hi,

I hope someone here can help me.

We have a product table which has a many-to-many relation
to a category table (joined through a third "ProductCategory" table):

[product] --< [productCategory] >-- [category]
--- ------ ----
productID productCategoryID categoryID
productName productID categoryName
categoryID

We want to get a view where each product occupies just one row, and
any multiple category values are combined into a single value, eg
(concatenating with commas):

Product Category
------
cheese dairy
cheese solid
milk dairy
milk liquid
beer liquid

will become:

Product Category
------
cheese dairy, solid
milk dairy, liquid
beer liquid

What is the best way to do it in SQL?

Thanks and regards,
Dmitri"mitmed" <mitmed@.yahoo.com> wrote in message
news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> Hi,
> I hope someone here can help me.
> We have a product table which has a many-to-many relation
> to a category table (joined through a third "ProductCategory" table):
> [product] --< [productCategory] >-- [category]
> --- ------ ----
> productID productCategoryID categoryID
> productName productID categoryName
> categoryID
> We want to get a view where each product occupies just one row, and
> any multiple category values are combined into a single value, eg
> (concatenating with commas):
> Product Category
> ------
> cheese dairy
> cheese solid
> milk dairy
> milk liquid
> beer liquid
> will become:
> Product Category
> ------
> cheese dairy, solid
> milk dairy, liquid
> beer liquid
> What is the best way to do it in SQL?
> Thanks and regards,
> Dmitri

The usual answer is that you should do this in the client, not in TSQL, but
if you must then the only reliable way is using a cursor.

http://www.aspfaq.com/show.asp?id=2279

Simon|||Thanks for your reply Simon,

I completely agree with you that the best place for this type of code
is on the client side. The issue is that my client side is a Crystal
Report (in VB.NET) and i don't know how to do this kind of processing
there. The report i'm trying to produce is the list of products and
their details including categories a product belongs to. I would
really appreciate if somebody could point me to a good crystal report
resource, where it shows how to do things like that if it's possible.

Regards,
Dmitri

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4124eaa5_2@.news.bluewin.ch>...
> "mitmed" <mitmed@.yahoo.com> wrote in message
> news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> > Hi,
> > I hope someone here can help me.
> > We have a product table which has a many-to-many relation
> > to a category table (joined through a third "ProductCategory" table):
> > [product] --< [productCategory] >-- [category]
> > --- ------ ----
> > productID productCategoryID categoryID
> > productName productID categoryName
> > categoryID
> > We want to get a view where each product occupies just one row, and
> > any multiple category values are combined into a single value, eg
> > (concatenating with commas):
> > Product Category
> > ------
> > cheese dairy
> > cheese solid
> > milk dairy
> > milk liquid
> > beer liquid
> > will become:
> > Product Category
> > ------
> > cheese dairy, solid
> > milk dairy, liquid
> > beer liquid
> > What is the best way to do it in SQL?
> > Thanks and regards,
> > Dmitri
> The usual answer is that you should do this in the client, not in TSQL, but
> if you must then the only reliable way is using a cursor.
> http://www.aspfaq.com/show.asp?id=2279
> Simon|||You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.

For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite @.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||mitmed (mitmed@.yahoo.com) writes:
> I completely agree with you that the best place for this type of code
> is on the client side. The issue is that my client side is a Crystal
> Report (in VB.NET) and i don't know how to do this kind of processing
> there. The report i'm trying to produce is the list of products and
> their details including categories a product belongs to. I would
> really appreciate if somebody could point me to a good crystal report
> resource, where it shows how to do things like that if it's possible.

We use Crystal in our system (and we hate it!), but we never let Crystal
near SQL Server itself. The "database" we tell Crystal about is text files
with all the columns. The actual queries are submitted from VB6, and then
we feed Crystal one of more recordsets, typically augmented with other stuff
that the VB code puts in.

Exactly how that translates to in VB .Net I don't know, although it is
possible to work with ADO Recordset if you use the OleDb .Net data
provider. Then again, who wants to use ADO recordsets if you are in .Net?

I should add that my notion of how we use Crystal is somewhat foggy. I
try to stay away from Crystal as much as I can.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to concatenate multiple rows into one

I want to Concatenate multiple rows into one string to show on the
report. Can someone help plz.Adnan,
U need to use Cursors...in the stored procedure.
Thats the only solution i know.....
"Adnan" wrote:
> I want to Concatenate multiple rows into one string to show on the
> report. Can someone help plz.
>

how to concat/pivot rows to column?

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP|||Originally posted by Pat Phelan
There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP

PatP, thanks for your reply. After posting I realize I should have included more information.
Here's more specifics:
CREATE TABLE [elements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[description] [varchar] (50) NULL ,
[code] [varchar] (5000) NOT NULL ,
[ord] [int] NOT NULL
) ON [PRIMARY]
GO

elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
so to build a table, i would select the code and order by the ord column. the result is similar to the following:
<table width="100%" border="0">
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
(10 rows).
I would like to query the table based on the parameter passed to return the same results, except in one record:
<table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
(1 row).

hope this helps clear it up|||That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

With that said, I'd start with:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

SELECT @.r = ''
DECLARE z1 CURSOR FOR SELECT [code]
FROM [elements]
WHERE name = @.name
ORDER BY ord

OPEN z1
FETCH z1 INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z1 INTO @.c
END

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP

many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
like: select + * from blah would concat results. ;)

i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

thanks again.

How to compare values in different rows?

Hi


I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay

Wednesday, March 7, 2012

how to compare rows from oldtbl to curtbl using Like?

oldtbl contains these CoNames
'abc'
'def'
'ghi'
'jkl'
curtbl contains these CoNames
'abc inc'
'def inc'
'jkl inc'
Note that curtbl does not contain 'ghi inc'
I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
select Coname from oldtbl where CoName Like (select CoName from curTbl)
select t1.CoName from oldtbl t1 join curtbl t2 On
t1.CoName Like t2.CoName --this did not return anthing
Is there a way to do this using Like? If yes, what is the correct syntax?
If not, is there another way to compare these rows?
Thanks,
RichI had my tables backwards. I need to retrieve rows from curtbl where CoName
is like oldtbl.CoName. I tried this syntax which actually did return rows,
but is quite slow.
select t1.CoName from curtbl t1 join oldtbl t2 on
t1.CoName like '%'+t2.CoName+'%'
My other alternative is to loop through oldtbl (all distinct CoNames) using
a cursor. I am just checking if there is a better way than a cursor.
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||then try this
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
"Rich" wrote:
> I had my tables backwards. I need to retrieve rows from curtbl where CoNa
me
> is like oldtbl.CoName. I tried this syntax which actually did return rows
,
> but is quite slow.
> select t1.CoName from curtbl t1 join oldtbl t2 on
> t1.CoName like '%'+t2.CoName+'%'
> My other alternative is to loop through oldtbl (all distinct CoNames) usin
g
> a cursor. I am just checking if there is a better way than a cursor.
>
>
> "Rich" wrote:
>|||Hi Omnibuzz, its me again :)
Thank you for your reply. I tried your method. It works, but wasn't any
faster than the one that I tried. But I believe your methos is probably mor
e
accurate. I did try a cursor, but I am still having a problem setting up
delimeters. I am still not clear on the rule for delimiting things in
Dynamic Sql. The one thing about the cursor is that I can print out a count
to let me know the progress of the cursor. Here is the cursor - which has a
syntax error in the sql statement. Any suggestions appreciated how to fix
this. Note: I am using the actual col names and table names here.
Declare @.coid varchar(50), @.i int
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp3
set @.i = 1
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.i
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
set @.i = @.i + 1
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
Thanks,
Rich
"Omnibuzz" wrote:
> use this.. and let me know if it works
> select a.CoName from oldTbl a
> where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName
+
> '%')
>
> --
>
>
> "Rich" wrote:
>|||Hi Rich..
I would say its better to use a query than this approach. try to index on
that column and see.
your approach is a double killer. a cursor and dynamic sql.. whooooh.. lets
keep the cursor out for this problem.
Hope this helps.
--
"Rich" wrote:
> Hi Omnibuzz, its me again :)
> Thank you for your reply. I tried your method. It works, but wasn't any
> faster than the one that I tried. But I believe your methos is probably m
ore
> accurate. I did try a cursor, but I am still having a problem setting up
> delimeters. I am still not clear on the rule for delimiting things in
> Dynamic Sql. The one thing about the cursor is that I can print out a cou
nt
> to let me know the progress of the cursor. Here is the cursor - which has
a
> syntax error in the sql statement. Any suggestions appreciated how to fix
> this. Note: I am using the actual col names and table names here.
>
> Declare @.coid varchar(50), @.i int
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp3
> set @.i = 1
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.i
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> set @.i = @.i + 1
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> Thanks,
> Rich
> "Omnibuzz" wrote:
>|||Well I am currently running
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
as we speak (as I type). On the cursor, I can limit my old table to like 10
CoNames. So I iterate only 10 times in the cursor. My thing is that I was
trying out the cursor just for exercise - mostly on writing dynamic sql. Ma
y
I request if you could correct my syntax on the sql statement?
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
I fetch the CoName into @.coid. I am trying to simulate
Insert Into #temp4 select recordID, coid from subscriber where coid like
'%abc%'
How do I delimit @.coid above ?
Thanks again for your help
Rich
"Omnibuzz" wrote:
> Hi Rich..
> I would say its better to use a query than this approach. try to index
on
> that column and see.
> your approach is a double killer. a cursor and dynamic sql.. whooooh.. let
s
> keep the cursor out for this problem.
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||use this
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like ''%''' + @.coid + '''%''')
Hope this helps.
--
"Rich" wrote:
> Well I am currently running
> select t1.CoName from curtbl t1 join oldtbl t2 on
> charindex('%'+t2.CoName+'%',t1.CoName) >0
> as we speak (as I type). On the cursor, I can limit my old table to like
10
> CoNames. So I iterate only 10 times in the cursor. My thing is that I wa
s
> trying out the cursor just for exercise - mostly on writing dynamic sql.
May
> I request if you could correct my syntax on the sql statement?
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> I fetch the CoName into @.coid. I am trying to simulate
> Insert Into #temp4 select recordID, coid from subscriber where coid like
> '%abc%'
> How do I delimit @.coid above ?
> Thanks again for your help
> Rich
>
> "Omnibuzz" wrote:
>|||Thank you. Well, the cursor ran this time, but I got an error message for
each of 10 iterations in the cursor. Note: I modified the cursor to only
select (and not insert - for if there is no match don't insert a null row.
Here is my modified cursor and the error message - using only the select
statment. Could it be that since none of the 10 CoNames I am passing in hav
e
a match in the current table ?
----
Declare @.coid varchar(50)
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp5
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
----
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'NEW'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TRENWICK'.
Server: Msg 170, Level 15, State 1, Line 1
...
Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
against:
'NEW CENTURY ENERGIES INC-CO'
'TRENWICK AMERICA CORP'
...
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual tbl
"Omnibuzz" wrote:
> use this
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like ''%''' + @.coid + '''%''')
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||Well, I modified the delimiters as follows which ended up working in the cur
sor
exec('select recordID, CoID from subscriber where CoID Like ''%' + @.coid +
'%''')
And when I used a smaller number of CoNames, your method
select a.coid from subscriber a
where exists ( select 1 from #temp6 b where b.coid like '%' + a.coid + '%')
worked very well. I really appreciate all of your help. Thank you very
much.
Rich
"Rich" wrote:
> Thank you. Well, the cursor ran this time, but I got an error message for
> each of 10 iterations in the cursor. Note: I modified the cursor to only
> select (and not insert - for if there is no match don't insert a null row.
> Here is my modified cursor and the error message - using only the select
> statment. Could it be that since none of the 10 CoNames I am passing in h
ave
> a match in the current table ?
> ----
--
> Declare @.coid varchar(50)
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp5
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> ----
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'NEW'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'TRENWICK'.
> Server: Msg 170, Level 15, State 1, Line 1
> ...
> Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
> against:
>
> 'NEW CENTURY ENERGIES INC-CO'
> 'TRENWICK AMERICA CORP'
> ...
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual t
bl
>
>
> "Omnibuzz" wrote:
>

Friday, February 24, 2012

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

How to Combine Multiple Rows Data into single Record or String based on a common field.

Hellow Folks.

Here is the Original Data in my single SQL 2005 Table:

Department: Sells:

1 Meat

1 Rice

1 Orange

2 Orange

2 Apple

3 Pears

The Data I would like read separated by Semi-colon:

Department: Sells:

1 Meat;Rice;Orange

2 Orange;Apple

3 Pears

I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

Hi,

you can use the following Function in SQL server:

USE NORTHWIND
GO

CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)

SELECT@.Products =COALESCE(@.Products +', ','') + ProductName
FROM Products
WHERE CategoryID = @.CategoryID
ORDER BY ProductNameASC

RETURN @.Products
END
GO

SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID)AS ProductList
FROM Products
GO

 
and this is based on your table: 
 
USE NORTHWINDCS
GO

CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)

SELECT@.Products =COALESCE(@.Products +', ','') + sells
FROM table1
WHERE Department = @.CategoryID
ORDER BY sellsASC

RETURN @.Products
END
GO

SELECTDISTINCT Department, dbo.ProductList (department)AS ProductList
FROM table1
GO

thanks

|||

SharpGuy, your solution works. Thanks and have a great Thanksgiving...

Sunday, February 19, 2012

How to clear table?

Is there a way to clear all the records in a table in Microsoft SQLServer Management Studio instead of selecting all the rows and delete?

Try the link below and Truncate the table. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

|||Thanks for the response, but I'm not looking for a code clear records.I am in Microsof SQL Management Studio. How do I clear the table withinthis tool?|||

Assuming no constraint defined on the table try the link below for a walkthrough. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175491.aspx

|||That instruction only show how to delete a column. Are you suggestingthat deleting the column and then recreate that same column?|||

Sorry wrong link.

http://msdn2.microsoft.com/en-us/library/ms190616.aspx

|||That link shows how to delete the whole tabe. I don't want to deletethe whole table. All I want is to clear all the records in the table. Idon't want to recreate the whole table again.|||Truncate is the clean way to do it or you have to do it by column because a database table is columns with your data in rows so if you don't want to truncate you have to do it by column.|||The reason I asked about clearing the table records is because inphpMyAdmin with MySQL, it is very simple to clear the whole records ina table. I would be very surprise if Microsoft does not have thisfeature in their MS SQL Management Studio tool.|||

MySQL is generally free so you can do crude things but SQL Server cost a lot of money companies will not like data delete operations that cannot be traced. So the links below explain Truncate and Delete very clearly and some solutions using temp table. Hope this helps.

http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/09/62960.aspx

http://www.sql-server-performance.com/q&a118.asp

|||Thanks! I will give those two links some reading.

How to clean (empty) a table?

Hi:
I have a table in a database, how to clean this table (remove all data
rows)? I guess there should be some SQL command to do that? There are
millions of rows, kind of hard to empty the table by hand.
Thanks for your helpDELETE tablename
or
TRUNCATE TABLE tablename
The latter is handy if there are no PK/FK relationships that it would break,
because (a) it is lightly logged, and (b) it also resets the seed for an
IDENTITY column, if one exists.
"Polaris" <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Thanks guys for your quick help!
Polaris <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Slight correction:
The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
it can reference other tables and it can have a primary key.
Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
and ddl_admin roles, which probably makes it a poor solution in production
systems.
Jacco
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> Polaris,
> TRUNCATE TABLE <tablename> is a good option if the tables doesnt have any
> PK/FK's.This is minimally logged and since you are dealing with millions
of
> rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
option
> left would be to do DELETE <tablename>.But here, do the DELETE in small
> batches and then truncate the transaction log in between so that you dont
> end up with a big log .
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Polaris" <etpolaris@.hotmail.com> wrote in message
> news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > Hi:
> >
> > I have a table in a database, how to clean this table (remove all data
> > rows)? I guess there should be some SQL command to do that? There are
> > millions of rows, kind of hard to empty the table by hand.
> >
> > Thanks for your help
> >
> >
>|||Hi all!
I am in need of writing a few stored procedures.
The first one is to create a stored procedure to recover a database from
backup and the second one is to create a stored procedure to execute a
transaction log backup (even though I know this can be done through a
maintainence plan). Any help would be greatly appreciated.
Thanks in advance!|||Jacco,
Thanks for the correction! My intention was same as you mentioned but my
wording was wrong.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23hraMMxRDHA.2196@.TK2MSFTNGP11.phx.gbl...
> Slight correction:
> The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
> it can reference other tables and it can have a primary key.
> Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
> and ddl_admin roles, which probably makes it a poor solution in production
> systems.
> Jacco
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> > Polaris,
> >
> > TRUNCATE TABLE <tablename> is a good option if the tables doesnt have
any
> > PK/FK's.This is minimally logged and since you are dealing with millions
> of
> > rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
> option
> > left would be to do DELETE <tablename>.But here, do the DELETE in small
> > batches and then truncate the transaction log in between so that you
dont
> > end up with a big log .
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Polaris" <etpolaris@.hotmail.com> wrote in message
> > news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > > Hi:
> > >
> > > I have a table in a database, how to clean this table (remove all data
> > > rows)? I guess there should be some SQL command to do that? There are
> > > millions of rows, kind of hard to empty the table by hand.
> > >
> > > Thanks for your help
> > >
> > >
> >
> >
>

How to choose the hardware for SQL Server wit large data

Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,000 5000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran7,000,000 is not a big table in database ...
I had one back in 2002 with more than 150,000,000 rows (in an automatic
partitionned view - ok) (Hardware : 2 processors, < 40GBytes).
By the way, you need to evaluate more points:
* peek number of simultaneous connections / peek value of 'count per minute'
* mean size of row data stored and returned
...
Sl.
"N Kiran Kumar" <N Kiran Kumar@.discussions.microsoft.com> a écrit dans le
message de news: 46BB099A-37CB-4D25-9728-1110CB9804CD@.microsoft.com...
> Hi,
> I have a large data base and data keeps adding daily. I have the
> current
> number of rows and row size. How do i calculate the hardware power
> required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>|||Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>

How to choose the hardware for SQL Server wit large data

Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,0005000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran
Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:

> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,0005000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>

How to choose next value for identity column

Hi,

I have a table with its ID field set as an Identity column with "seed" and "increment" set to 1. Now, I have rows in there that I don't want to change. I'd like the next addtitions to the table to start at a specific value for their ID columns.

Basically, how can I specify a new starting value for an identity column?

Thanks,

Skip.I found this in SQL Server books online as well as the web. This sounds like what you might be looking for.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5lv8.asp