Merge results from two fields

What is the best way to go about merging the results of two fields in
the same table?

I have two different fields that hold lists of names, some of them
identical, some different. From those I'd like to generate a merged
list with all the distinct names.

For example:

Field1 contains: Jack, Joe, Jim, Paul
Field2 contains: Peter, Paul, Joe, Jim

The result should be: Jack, Joe, Jim, Peter, PaulWhy are you storing delimited lists in columns? This is a bad idea in a
relational database. Best to create a new table and put the data in
there. SELECT DISTINCT will give you a distinct list from a table but
it's tricky to manipulate delimited lists in SQL.

--
David Portas
SQL Server MVP
--|||I'm sorry, I meant to convey that these names are different records in
the particular fields.

So - I may have the following records:

<pre>
Rec# Field1 Field2
1: Jack Peter
2: Joe Paul
3: Jim Joe
4: Paul Jim
</pre
and would like a result of:
Jack
Joe
Jim
Paul
Peter|||Create table statements and insert statements for sample data will
prevent these kinds of misunderstandings. In the future, please try to
include those in your posts.

Off the top of my head (meaning it might not be the best solution
performance-wise), the following should work:

CREATE TABLE My_Table (field1 VARCHAR(20), field2 VARCHAR(20))
GO

INSERT INTO My_Table VALUES ('Jack', 'Peter')
INSERT INTO My_Table VALUES ('Joe', 'Paul')
INSERT INTO My_Table VALUES ('Jim', 'Joe')
INSERT INTO My_Table VALUES ('Paul', 'Jim')
GO

SELECT Field1 AS Name FROM My_Table
UNION
SELECT Field2 FROM My_Table
GO

HTH,
-Tom.|||This should do it

select distinct Field1 from TableName
union
select distinct Field2 from TableName

Stacey wrote:
> I'm sorry, I meant to convey that these names are different records
in
> the particular fields.
> So - I may have the following records:
> <pre>
> Rec# Field1 Field2
> 1: Jack Peter
> 2: Joe Paul
> 3: Jim Joe
> 4: Paul Jim
> </pre>
> and would like a result of:
> Jack
> Joe
> Jim
> Paul
> Peter|||Do not use SELECT DISTINCT and UNION together. It is redundant and
most SQL products will not see that, so you get three sorts instead of
one.

Merge Replication Without Merging Client To Server

I want to SYNC the data on the server to the client, but never the client data to the server.

Is such a configuration possible with Merge Replication under SQL CE?

Thanks again!

Merge is designed to be bidirectional but RDA is intended for scenarios like yours.

Remote Data Access (RDA) with tracking turned OFF on your SQL Mobile tables is the way to solve this. You already have the IIS infrastructure in place since you're doing merge today. RDA pull the data to SQL Mobile on some schedule that supports the way your mobile users operate.

-Darren

|||

If you are talking about SQL Mobile 3.0 and SQL Server 9.0. YES :)

Choose the article type as "Download Only". This way you always get changes from SQL Server down to SQL Mobile. However, your changes in SQL Mobile will not be sent to SQL Server.

Useful MSDN URL: http://msdn2.microsoft.com/en-us/library/ms147295.aspx

Thanks,

LaxmI NRO, MSFT, SQL Mobile, Microsoft Corporation

Merge Replication not merging updates made to subscriber

Hope someone can help with this. I've implemented merge replication and when
I make an update to the server that acts as the distributor the update is
reflected on the subscribers. However when I make a change on the subscriber
I cannot see it on the other subscribers or on the distributor. Any help
regarding this would be appreciated.
Thank you,
Abdul Rauf
Abdul,
how are you making the change on the subscriber? If it is a bulk insert
without firing the triggers, it won't replicate to the publisher. In some
rare other cases this also happens (eg when the filter is set to 1=2 and you
are adding records onto the subscriber. If there is no coresponding record
in MSmerge_contents try using sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Paul, I'm new to replication so I will do more research on what you have
below. I'm testing this scenario so I'm just going into the enterprise
manager of the subscriber database and updating a row in the Pubs database.
Updating a row in the Distributor database works but not in the Subscriber
through enterprise manager.
"Paul Ibison" wrote:

> Abdul,
> how are you making the change on the subscriber? If it is a bulk insert
> without firing the triggers, it won't replicate to the publisher. In some
> rare other cases this also happens (eg when the filter is set to 1=2 and you
> are adding records onto the subscriber. If there is no coresponding record
> in MSmerge_contents try using sp_addtabletocontents to include the rows then
> resynchronise. Alternatively you can use sp_mergedummyupdate for a single
> row.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>

Merge replication not merging changes

my replication scenario involves a table that has a filter on three columns.
I update this table and the data does not get propogated to the subscriber.
effectively, I am creating "deletes" at the subscriber by invalidating the
data (i.e., updates cause it to fall outside the filter criteria)
The initial subscription works. Then I update so the data should be removed
and it doesn't happen.
I use sp_showrowreplicainfo and I can see that the generation info at the
subscriber is lower than that at the publisher.
I initiate the merge and the data does not get removed from the subscriber.
It's very late for me, so maybe I am missing something obvious.
Suggestions appreciated
regards
Steve
To answer my own question after further experimentation, it appears to depend
completely on which table you hang the join filter off.
If the parent table has its referenced row updated, the change cascades
properly down the chain.
Wow, what a tiring lesson to learn.
"SteveM" wrote:

> my replication scenario involves a table that has a filter on three columns.
> I update this table and the data does not get propogated to the subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
>
|||Generation numbers are localized to the database and article. If you have
more than one subscriber the generation values for the same article on the
publisher and subscriber can vary wildly. IIRC the generation value for the
publisher in a single subscriber topology will always be one larger than the
value on the subscriber.
Check the conflict viewer to see if there is anything there.
Hilary Cotter
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
"SteveM" <SteveM@.discussions.microsoft.com> wrote in message
news:0AC4EB4D-11DD-40C5-A1B3-7160A559B57B@.microsoft.com...
> my replication scenario involves a table that has a filter on three
> columns.
> I update this table and the data does not get propogated to the
> subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
> The initial subscription works. Then I update so the data should be
> removed
> and it doesn't happen.
> I use sp_showrowreplicainfo and I can see that the generation info at the
> subscriber is lower than that at the publisher.
> I initiate the merge and the data does not get removed from the
> subscriber.
> It's very late for me, so maybe I am missing something obvious.
> Suggestions appreciated
> regards
> Steve