Merge Tables On Unique Key Using Different Columns
May 15, 2008
hi guys,
i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text
ID1+ID2+ID3 is the clusterd unique key in each table
what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1
what i tried is to get all possible rows with
SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3
but i dont know how to join or add the other columns. maybe with
WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...
any ideas?
thx
View 10 Replies
ADVERTISEMENT
Oct 26, 2015
I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.Â
declare
@start datetime,
@end datetime,
@campaignId uniqueidentifier,
@campaignName varchar(250),
@segment varchar(50)
set @start = '2015-10-07'
set @end = '2015-10-20'
[code]....
how to merge these two columns from #sales and #hours tables without duplication.
View 6 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Jul 2, 2015
Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.
I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".
   INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname)
   SELECT KontaktID, AnredeID, Titel, Nachname
   FROM [Susi].MSCMS.Core.tKontakte AS Client
   WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);
[Code] ....
View 8 Replies
View Related
Jan 26, 2006
Hi,
I have a slight problem which I'm sure must be a common happening. Here's my problem.
I'm using Merge replication and I have a table the has a unique contraint on a non primary key column (the column is called [name]). The thing that goes wrong (for me) is that when a new record is added in a subscriber and a new record is added in the publisher before a synchronization and both records have the same [name] value then when the merge agent runs I get an unresolved conflict because of a unique index violation.
I've read the BOL and I'm left thinking that in order to solve this problem then I must use a custom resolver. Is this the best way of handling such a conflict? Actually if it is I'm still a little stuck as I'm not sure what I could do to help the situation inside the custom resolver anyway!!
Any help would be much appreciated.
Thanks
Graham
View 3 Replies
View Related
Jun 2, 2015
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET
USING tblTemp AS SOURCE
ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN
UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN
INSERT (serverName, serverLocation)
VALUES (SOURCE.serverName, SOURCE.serverLocation)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
View 3 Replies
View Related
Mar 23, 2002
We are currently attempting to (Merge) replicate a database between 2 servers; distributor and subscriber. Apparently there are no Primary Keys in the database, and without the input of the developers, we can only assume that data integrity must be maintained by the application and the database is somewhat generic. In any case, what we are experiencing is that when we attempt to replicate, and you may know, you need PKs. Without any, SQL Server has utilized it's own (Row GUIDs). The Snapshot worked fine, but the merge agent reports error 1505.
The error is "CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID %d. Most significant primary key is '%S_KEY'."
The fix and workaround are SP2 and deselecting Unique in the index propoerties of the index raising the error. We already had SP2 on the system. Tried the other and still the same issue occurs.
Microsoft's Knowledgebase has nothing. Any suggestions or fixes would be greatly appreciated.
Regards,
Alex Cohen (MCSE, MCDBA, MCT)
View 1 Replies
View Related
Feb 21, 2008
Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record. Graciaz,Nhoyti
View 3 Replies
View Related
Feb 13, 2004
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
Any pro's con's appreciated,
Dan
View 8 Replies
View Related
Mar 20, 2013
I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)
What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.
So that I don't have duplicate candidate ids and i have single row with all column data.
View 6 Replies
View Related
Apr 19, 2013
I find to be able to have multiple NULL entries in the following constraint:
Code:
CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
But is not possible to check multiple columns in "WHERE".
I using SQL Server 2012 Express
View 3 Replies
View Related
Mar 7, 2006
Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.
2. I am using an identity column (INT)to create a unique value within the table
3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)
many thanks in advance
ICW
View 5 Replies
View Related
Aug 29, 2006
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS??? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
Thank you very much
View 1 Replies
View Related
May 17, 2007
Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows
Any thoughts?
Jorge3921
View 14 Replies
View Related
Feb 12, 2006
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
View 4 Replies
View Related
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
Oct 31, 2005
Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,
View 4 Replies
View Related
Jul 20, 2005
Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
View 3 Replies
View Related
Feb 9, 2007
Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.
Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.
What's the recommend method to do this now - since row level processing of variables seem to be a no-no?
View 5 Replies
View Related
May 31, 2007
In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships. However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows. I'm hoping someone can provide some advice on how to best handle this situation.
Here is an example to work from. Suppose we have a table that contains job positions. The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted". This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company. You can substitute your own scenarios here as there are quite a few.
Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want. However we will want to be able to "delete" a position but later add a new position with the same name. One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option. Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name. We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.
My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column. The UniqueName column is set (via a trigger) to the Name column when the position is active. When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness. This is handled by the system and not exposed to clients.
Any advice or alternatives on this approach (preferably with advantages and disadvantages)? Thanks.
Michael Taylor - 5/31/07
http://p3net.mvps.org
View 4 Replies
View Related
Aug 14, 2006
We have an app that threads together emails coming out of Exchange, using their messageid. To ensure threading works correctly, we need to ensure uniqueness of messageid, which we do with a unique index (we also need to be able to lookup by messageid when a message comes in).
We are currently porting the app from Oracle and PostgreSQL to SQL Server and are having problems with the 900 byte max length of an index. The problem is that the maximum size of a messageid (according to the Exchange docs) is 1877 bytes.
How can we guarantee uniqueness?
View 7 Replies
View Related
Mar 18, 2007
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA
05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
View 12 Replies
View Related
Jan 19, 2008
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
View 4 Replies
View Related
Apr 3, 2000
I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?
View 1 Replies
View Related
Dec 30, 2004
I try to search my data and sort the result by importance.
I'm using a MS Access database and my data (table1) looks like this:
Code:
ID NAME TEXT
1 Apples Good red apples
2 Bananas Fine yellow bananas
3 Yellow apples Great yellow apples
I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:
Code:
SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder
The output is this:
Code:
ID NAME TEXT SEARCHORDER
3 Yellow apples Great yellow apples 1
2 Bananas Fine yellow bananas 2
3 Yellow apples Great yellow apples 2
So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.
Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?
Best regards,
Peter from Denmark
View 2 Replies
View Related
Jun 20, 2008
Hi Guys.
I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?
For Example:
This is what I want
ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2
This is what I DO NOT want:
ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null
I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.
View 1 Replies
View Related
May 5, 2008
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
View 4 Replies
View Related
Jun 20, 2006
I have 2 columns 'Col1' (char) and 'Col2' (integer)
I'd like to combine these 2 columns and put the result in 'Col3' (char)
please help!!!
View 5 Replies
View Related
Jun 16, 2007
I want to combine a column with Id and other with Name.
For eg: If Id is 01 and Name is 'Tom', the output should be 01-Tom.
I am not able to execute it using || or CONCAT since Id has the data type int and Name is a string.
Do you have a solution?
`RG`
View 4 Replies
View Related
Dec 7, 2007
hi guys,
i have a table with 2 columns say:
First_Name Last_Name
David Reinjal
i need to merge these 2 columns and put into a single column with a ','(comma) seperated. it will look loke this:
column_name
David, Reinjal
how can i do this? can anybody help me in this?
thanks,
David
View 4 Replies
View Related
Aug 3, 2007
I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column
View 8 Replies
View Related
Feb 4, 2008
Hi!
I'm using IS with SQL Server 2005.
I need to know how to merge two columns (or more) into a single on as output.
For example:
#ID#Value1#Value2#
would be the input table. Now I want to map the column Value1 as 'Value' - as well as the column 'Value2' so there is only one output - and two rows generated by one row of the originate table.
I tried to map those columns to the same name, but IS refused that. My aim is to transform data from an old table to a new, relational one where all those values have to fit into relational tables (n:m relations).
How do I do that?
Thank you in advance!
View 3 Replies
View Related
Nov 7, 2006
Hello everybody!
I´m trying to put together two columns of integer into one. They are coming from the same source table, so the "merge"-Task doesn´t work. This is what they look like:
column1: <123>
column2: <0123456>
The result should be:
merged_column: <1230123456>
Is there a task for that kind of operation or do I have to write a script? I would like do do that within the data flow.
thanks in advance!
View 5 Replies
View Related
