ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,391 through 6,405 (of 10,143 total)

RE: Insert Into With Count()

In grown-up SQL Server folks often use
RAISERROR('Almost instant screen print',10,1) WITH NOWAIT;
rather than PRINT.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 8:46 am

#1418171
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/7/2011)
Hi Chris ..is it possible to get the output in a different way.
I want the heading to be like
TYPE BANKNAME ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 8:36 am

#1418154
RE: Insert Into With Count()

Rixxe (12/7/2011)
ChrisM@Work (12/7/2011)
Let me ask another question. This looks like a single step in the creation of a complex cross-tabbed report. Is this the case?
If so, is the data you're...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 7:15 am

#1418072
RE: need sql query to convert a column of a table into meaningful data

Ah, an overrun, from the bit which records the position in the substring of '='.
SELECT
[No.],
MsgType,
MsgTimeStamp,
split.Item,
Pos,
en.[Entity code],
en.[Entity name],
Attribute
FROM #SampleTable s
CROSS APPLY dbo.DelimitedSplit8k(s.[Description],'') split
CROSS APPLY (
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 6:40 am

#1418041
RE: need sql query to convert a column of a table into meaningful data

It's possibly worse than that:
56=DEB 34=293131=0018000 47992N1 297=5
There are clearly defined entity-attribute pairs at the beginning and the end, but what's in between? Even by eye it's not possible to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 5:59 am

#1418006
RE: need sql query to convert a column of a table into meaningful data

Could be tab. Try copy-paste into a function:
SELECT ASCII('')
SELECT CHAR(7)
Copy and paste the space between the quotes in this - SELECT ASCII('') and run it in SSMS.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 5:41 am

#1417990
RE: need sql query to convert a column of a table into meaningful data

You're welcome.
The function looks up to date. Where are we with this? Did you change the sample data to uniquely identify each row?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 5:26 am

#1417982
RE: need sql query to convert a column of a table into meaningful data

It's fine for now Mario. There's a slightly newer, faster version, I'll find a link. Or the code.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 5:04 am

#1417968
RE: need sql query to convert a column of a table into meaningful data

Many thanks, Mario, that's just about perfect. Here's your table integrated into that code I posted earlier. First thing you will notice is that the data requires partitioning - there's...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 4:48 am

#1417958
RE: Insert Into With Count()

Let me ask another question. This looks like a single step in the creation of a complex cross-tabbed report. Is this the case?
If so, is the data you're inserting into...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 4:37 am

#1417952
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/7/2011)
Hope this information is enough for you chris? thnks
Have you tried running the sample table code?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 4:30 am

#1417946
RE: Insert Into With Count()

INSERT...SELECT will insert rows from the output of the SELECT. If there are no rows returned by the SELECT, then no rows will be inserted.
In your case, you want...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 4:23 am

#1417939
RE: need sql query to convert a column of a table into meaningful data

Heh Mario, take a look at the second paragraph in the forum etiquette article I pointed out to you! You might then read the third 😛
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 3:48 am

#1417924
RE: Subquery optimisation

Hi Paul
Have a read of Gails blog post on the subject of non-existence here[/url]. Armed with your new knowledge you will choose the best option.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 3:39 am

#1417918
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/7/2011)
sorry mike i may sound stupid but where do i get object dbo.Split. and second question is that declaring @message get only on row but in my table if...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 7, 2011 at 3:31 am

#1417910

Viewing 15 posts - 6,391 through 6,405 (of 10,143 total)