convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
[url]http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm[/url]
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/...20Beta%20II.htm
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

convert a linklist to relational database

I have a database has the following linklist structure. A, B, B1, B2 and B3 are records.
A is a group and the others are group members
***************************************
A.PointertoB
B.ForwardPointertoB1
B1.ForwardPointertoB2
B2.ForwardPointertoB3
B3.end
***************************************
I want to convert them to relational database structure so I need to find following pointers

B.PointertoA
B1.PointertoA
B2.PointertoA

Can I use sql to find the pointers? Thanks in advance.

Hi,

In your exisiting system do you have the ability to extract data out as char or text etc. if yes you can extract the data out,

Import all of it into a SQL table and then a write a T-SQL routine that will manipulate or extract data out of it. Once your happy that it works you can embedd the routine within DTS or SSIS.

I would do this. Some one might have a different idea.

There might also be third party tools that you can use.

Jag

Conversation LIFETIME

I have a conversation that I want to know has not ended so I am using LIFETIME.

When the conversation times out I then have three records in the queue.

1. The original conversation record that has not been received.

2. Error message to the initiator.

3. Error message to the target.

Both message bodies on the error records say that it was a lifetime error.

If I end the conversation on the initiator side after it is sent, I still get the target error record but the message_body field is null.

So say I don't end the conversation on the initator side. My next receive on the target side will pull the original record. Then it will pull the initator record and then it will pull the target record. Nothing on that record says that it had timed out.

What is the best practice for handling lifetime errors?

In the case when you end the conversation from the initiator side you are no seeing an Error message, but an EndDialog message (different message_type_name, no message_body). Because the conversation is ended, it will no longer expire.

To better understand the Service Broker semantics, I recommend you experiment with two separate services on two separate queues. The Broker is a communication mechanism between services, not a primitive for implementing a queue. If you are looking to create a queue, you'll be better of with a user table and proper enqueue/dequeue stored procs.

Can you explain what is application you're trying to build using Broker? We can help you better if we understand what is your goal.

Thanks,
~ Remus

sqlsql

Controlling the cursor position

Let's say I run a query that returns a 100k records. Is there a way to
control where the cursor is in the result set. For example, can I get it
to jump to row 50k and return the next 500 rows?Taken from Transact SQL help:
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor
Hope that helps
"preston" wrote:

> Let's say I run a query that returns a 100k records. Is there a way to
> control where the cursor is in the result set. For example, can I get it
> to jump to row 50k and return the next 500 rows?
>|||Hi, preston
See: http://www.aspfaq.com/show.asp?id=2120
Razvan

Continuous nonstop pumping of data

I have one data pump in a series that was pumping in too many records. Doing an independent query of the source table, I found there was about 140,000 records. My pump uses a variable for the source query, nothing fancy just a simple SELECT * FROM table WHERE DateField > '4/6/2006 12:00:00AM'. The Destination is local on the SQL Server and is set by a variable, and does a fast load. When I went away and checked in BIDS while it was running (the data flow tab where you can see the record count) it was at 28,000,000 and still going!

Any ideas what could be causing this? As I say there are only 140,000 records and no joins in the query--is this a bug someone has run into before?

Thanks,

Kayda

Sounds like you are using a variable to populate the SQL query for the date constraint, but that it isn't getting populated correctly.|||Actually, even without the date constraint the number of records is only 280,000 or so. Actually, I see that this is happening when it loses its connection to the database. This package has to access information through different dialup connections, and these connections are dialed through code in the package. It seems when the dialup connection is dropped then the datapump task that is running just appears to keep going infinitely, saying it is pumping millions of records. Any idea why this would happen and the pump would not just fail?

Containstable problem

Hi,
I have a question regarding the contains and containstable.
I have a table with about 2.000.000 records.
A the begining we simply use a "select top 1000 * from tablex where
contains(columnx,'xxx')" to find records matching the keywords enter by
users.
But, we find some performance problems when performing queries on keywords
that appears a lot of time
in the table (like "men", "women", "car", ...). The query take more than a
minute.
So we change our query to use the containstable to limit the reseach and it
worked fine and it only take few seconds. (Perfect)
But now, we wanted to add a where clause in the select to limit the reseach
according to a value of a field (not full text)
So, the query was something like this :
SELECT TOP 1000 ID FROM tableX AS FT_TBL
INNER JOIN CONTAINSTABLE (tableX, FT_INDEX , 'men',1000) AS KEY_TBL ON
FT_TBL.ID = KEY_TBL.KEY
WHERE SOURCE = 'news'
But, I notice that the containstable limit the result the 1000 first
occurences of 'men' and after that the where source='news' is applied.
So, of the 1000 records I only have about 500 with a "men" in the ft_index
and source='news'. But in the DB I have more than 2000 records with "men"
and "news"
Is there any way to combine the where clause and benefit of the limit of the
containstable ?
My hope is to have a select distinct top 1000 it from tablex where
source='news' and contains(ft_index,'men',1000)
Thanks
Stephane
No, unfortunately not. When you have restictions in your where clause you
should if at all possible partition your data into different tables.
So you would have a table which would contain all the rows with a source
value of news.
The problem is you get 1000 or less rows returned from MSSearch and then you
trim based on the value of the source column.
So suppose you have 2000 rows which match your search condition, and the
first 1000 rows don't have a source value of news. Such a search would
return 0 results.
The other option is to increase the value of your top_n_by_rank to a value
which guarantees 1000 rows after being filtered by the source restiction.
Normally you don't have the luxury of knowing a value of top_n_by_rank to
guarantee this though.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Stdu" <stdu@.myrealbox.com> wrote in message
news:%233pOC93wEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a question regarding the contains and containstable.
> I have a table with about 2.000.000 records.
> A the begining we simply use a "select top 1000 * from tablex where
> contains(columnx,'xxx')" to find records matching the keywords enter by
> users.
> But, we find some performance problems when performing queries on keywords
> that appears a lot of time
> in the table (like "men", "women", "car", ...). The query take more than a
> minute.
> So we change our query to use the containstable to limit the reseach and
it
> worked fine and it only take few seconds. (Perfect)
> But now, we wanted to add a where clause in the select to limit the
reseach
> according to a value of a field (not full text)
> So, the query was something like this :
> SELECT TOP 1000 ID FROM tableX AS FT_TBL
> INNER JOIN CONTAINSTABLE (tableX, FT_INDEX , 'men',1000) AS KEY_TBL ON
> FT_TBL.ID = KEY_TBL.KEY
> WHERE SOURCE = 'news'
> But, I notice that the containstable limit the result the 1000 first
> occurences of 'men' and after that the where source='news' is applied.
> So, of the 1000 records I only have about 500 with a "men" in the ft_index
> and source='news'. But in the DB I have more than 2000 records with "men"
> and "news"
> Is there any way to combine the where clause and benefit of the limit of
the
> containstable ?
> My hope is to have a select distinct top 1000 it from tablex where
> source='news' and contains(ft_index,'men',1000)
> Thanks
> Stephane
>
>
|||> Is there any way to combine the where clause and benefit of the limit of the
> containstable ?
> My hope is to have a select distinct top 1000 it from tablex where
> source='news' and contains(ft_index,'men',1000)
In Sql2000 I had to do the following (using your example).
Create another column that appended the column(s) that contained the
searched text with a special word or two like "xxxSource_news" and
include this new keyword in the search.
I just posted a question myself... if what we're looking for is
available in Sql2005.
|||This is a nice solution. However it only works with equality. For instance
you can't use if for quantities, ie where you have a where quantity> 1000.
But a very nice solution none the less.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Brian Rice" <mail@.brianrice.com> wrote in message
news:dce86229.0411060843.2d432c70@.posting.google.c om...[vbcol=seagreen]
the
> In Sql2000 I had to do the following (using your example).
> Create another column that appended the column(s) that contained the
> searched text with a special word or two like "xxxSource_news" and
> include this new keyword in the search.
> I just posted a question myself... if what we're looking for is
> available in Sql2005.
|||I will try the Brian's solution as I already have a field where I store all
my keywords to avoid the problem of the contains on multiple fields
Great idea
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OZ0sgfDxEHA.824@.TK2MSFTNGP11.phx.gbl...
> This is a nice solution. However it only works with equality. For
> instance
> you can't use if for quantities, ie where you have a where quantity> 1000.
> But a very nice solution none the less.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Brian Rice" <mail@.brianrice.com> wrote in message
> news:dce86229.0411060843.2d432c70@.posting.google.c om...
> the
>

containstable not returning accurate results

Does anyone lese get the same results that I do?

tblTest has an id field and a testName field....table has 2 records:

1 kevin test 1

2 kevin test 3

SELECT *

FROM tblTest t

INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]

Result is both records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?

Hi,

Numbers as considered as Noise words by Full-Text engine and are removed from Search pattern. So, your 2 records match your query.

Jean-Pierre Riehl

|||

Thanks....I knew about noise words, but I did not know that it was in effect even with quotes.

CONTAINS and WHERE Clause Combination taking too long

Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3

FROM

tbl

WHERE

col1 = '1234'

AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

As of now, Fulltext index runs separately from the SQL Engine which means that you cannot influence the to be parsed subset of data on the fulltext catalog. Although the data from the relational query will only bring back 1 row, the whole fulltext will be parsed to findt he appropiate matches although they will be discarded later upon joining the two resultsets.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks, i was already affraid that it will be the case.
In this case what you suggest? Because LIKE '%%' is killing my performance.
Any suggestion will be appreciated.

|||

Make sure you optimized the speed of Fulltext (like separate spindles etc).

Jens K. Suessmeyer

http://www.sqlserver2005.de

CONTAINS

Hi,
I would like to perform a search which returns records that are like a
string variable I pass into the query - so to catch spelling mistakes etc.
Eg. string = Commmercial would return Commercial from my reference table.
Could someone recommend the best way to do this. I am using VB.NET and
ADO.NET to execute my sql statement.
Should I use a WHERE CONTAINS clause. If so how does one enable full-text
indexed? I always get this error even using Northwind. "Cannot use a
CONTAINS or FREETEXT predicate on table 'mytable' because it is not
full-text indexed."Have a look at the SOUNDEX command, it may help.
Peter
"11Oppidan" wrote:

> Hi,
> I would like to perform a search which returns records that are like a
> string variable I pass into the query - so to catch spelling mistakes etc.
> Eg. string = Commmercial would return Commercial from my reference table.
> Could someone recommend the best way to do this. I am using VB.NET and
> ADO.NET to execute my sql statement.
> Should I use a WHERE CONTAINS clause. If so how does one enable full-text
> indexed? I always get this error even using Northwind. "Cannot use a
> CONTAINS or FREETEXT predicate on table 'mytable' because it is not
> full-text indexed."
>
>|||Thanks Peter - thats great!
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:23F80AF0-26B9-48F4-9501-290999F78877@.microsoft.com...
> Have a look at the SOUNDEX command, it may help.
> Peter
> "11Oppidan" wrote:
>

Constraint question

I have 'Table1' with two fields:
- RecID
- POID
I want to allow records to be entered in Table1 where the same RecID and
POID can exist across multiple records in Table1, but I don't want to allow
a POID to exist in Table1 twice with different RecIDs.
This is OK
RecID | POID
1 | 100
1 | 100
This is not OK
RecID | POID
1 | 100
2 | 100
What's the best way to set this up?
Thanks,
CB1. add another table with constraints PK(RecID, POID ) and
UNIQUE(POID) or vice versa
2. add a FK to table1 referencing that another table (RecID, POID )
P.S. there could be a problem with your database design. you might want
to post your DDL|||On Mon, 3 Oct 2005 13:28:22 -0400, Chris Burgess wrote:
(snip)
>This is OK
>RecID | POID
>1 | 100
>1 | 100
Hi Chris,
No it's not. Unless you have more columns in the table, but omitted them
in this post.
But with this design, you've now got two exact duplicates. And SQL gives
you no way to address just one of them. Every change you wish to make to
one of these rows will be made to the other as well.
If the reality you are modeling has two entities with RecID 1 and POID 1
and you've got a way to distinguish them, then add an extra column to
store whatever distinguishes the two in your business. If you've got the
both of them, they are really indistiguishable, but you still need to
store the fact that there are two of them, add a columns to hold the
current number. That solves your constraint problem as well:
CREATE TABLE YourTable
(RecID int NOT NULL,
POID int NOT NULL,
NumberOfItems int NOT NULL DEFAULT 1,
PRIMARY KEY (POID),
CHECK (NumberOfItems >= 1)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Consolidating Records

Let's say I have two tables:

CREATE TABLE dbo.OldTable
(
OldID int NOT NULL,
OldNote varchar(100) NULL
) ON [PRIMARY]
GO

AND

CREATE TABLE dbo.NewTable
(
NewID int NOT NULL IDENTITY (1, 1),
OldID int NULL,
ComboNote varchar(255) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.NewTable ADD CONSTRAINT
PK_NewTable PRIMARY KEY CLUSTERED
(
NewID
) ON [PRIMARY]

GO

OldTable's data looks like this:

OldID OldNote
-- ---
1 aaa
2 bbb
3 ccc
2 ddd
4 eee

NewTable's data (which is derived from the OldTable) should look like
this:

NewID OldID ComboNote
-- -- ---
1 1 aaa
2 2 bbb + char(13) + ddd
3 3 ccc
4 4 ddd

How can I combine the notes from OldTable where two (or more) records
have the same OldID into the NewTable's ComboNote?Something like this (untested)

select o1.OldID,o1.OldNote + char(13) + coalesce(o2.OldNote) as
ComboNote from OldTable o1
left join OldTable o2 on o1.OldID =o2.OldID
and o1.OldNote <> o2.OldNote

http://sqlservercode.blogspot.com/|||imani_technology_spam@.yahoo.com wrote:
> Let's say I have two tables:
> CREATE TABLE dbo.OldTable
> (
> OldID int NOT NULL,
> OldNote varchar(100) NULL
> ) ON [PRIMARY]
> GO
>
> AND
> CREATE TABLE dbo.NewTable
> (
> NewID int NOT NULL IDENTITY (1, 1),
> OldID int NULL,
> ComboNote varchar(255) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE dbo.NewTable ADD CONSTRAINT
> PK_NewTable PRIMARY KEY CLUSTERED
> (
> NewID
> ) ON [PRIMARY]
> GO
> OldTable's data looks like this:
> OldID OldNote
> -- ---
> 1 aaa
> 2 bbb
> 3 ccc
> 2 ddd
> 4 eee
>
> NewTable's data (which is derived from the OldTable) should look like
> this:
> NewID OldID ComboNote
> -- -- ---
> 1 1 aaa
> 2 2 bbb + char(13) + ddd
> 3 3 ccc
> 4 4 ddd
> How can I combine the notes from OldTable where two (or more) records
> have the same OldID into the NewTable's ComboNote?

You could look at a crosstab query, but if the number of old rows for
each new row is unknown, then it's quite awkward to do in pure TSQL. A
cursor might be the best server-side solution, although using a
client-side script may be easier.

But storing multiple values in a single column is usually bad design,
and it's often difficult to query columns like that efficiently. Perhaps
you should consider generating and formatting ComboNote in the front end
when you retrieve it, rather than storing it in the database, but
obviously I don't know your environment and application, so you may have
a good reason for keeping it as a single column.

Simon|||I agree with you. Unfortunately, that is what the clients want and I
don't think they can be talked out of it.

Simon Hayes wrote:
> imani_technology_spam@.yahoo.com wrote:
> > Let's say I have two tables:
> > CREATE TABLE dbo.OldTable
> > (
> > OldID int NOT NULL,
> > OldNote varchar(100) NULL
> > ) ON [PRIMARY]
> > GO
> > AND
> > CREATE TABLE dbo.NewTable
> > (
> > NewID int NOT NULL IDENTITY (1, 1),
> > OldID int NULL,
> > ComboNote varchar(255) NULL
> > ) ON [PRIMARY]
> > GO
> > ALTER TABLE dbo.NewTable ADD CONSTRAINT
> > PK_NewTable PRIMARY KEY CLUSTERED
> > (
> > NewID
> > ) ON [PRIMARY]
> > GO
> > OldTable's data looks like this:
> > OldID OldNote
> > -- ---
> > 1 aaa
> > 2 bbb
> > 3 ccc
> > 2 ddd
> > 4 eee
> > NewTable's data (which is derived from the OldTable) should look like
> > this:
> > NewID OldID ComboNote
> > -- -- ---
> > 1 1 aaa
> > 2 2 bbb + char(13) + ddd
> > 3 3 ccc
> > 4 4 ddd
> > How can I combine the notes from OldTable where two (or more) records
> > have the same OldID into the NewTable's ComboNote?
> You could look at a crosstab query, but if the number of old rows for
> each new row is unknown, then it's quite awkward to do in pure TSQL. A
> cursor might be the best server-side solution, although using a
> client-side script may be easier.
> But storing multiple values in a single column is usually bad design,
> and it's often difficult to query columns like that efficiently. Perhaps
> you should consider generating and formatting ComboNote in the front end
> when you retrieve it, rather than storing it in the database, but
> obviously I don't know your environment and application, so you may have
> a good reason for keeping it as a single column.
> Simon|||In that case you can write a while loop or a cursor
Is this a one time thing?

http://sqlservercode.blogspot.com/|||That's helpful, but what if there are more than two records that have
the same OldID that need to go into the NewTable's ComboNote?|||Yes, this should be a one-time thing. We are doing this to migrate
some data.

I think I'll take your advice and look into cursors, although I was
taught that cursors are the work of the devil.

SQL wrote:
> In that case you can write a while loop or a cursor
> Is this a one time thing?
> http://sqlservercode.blogspot.com/