Transact SQL :: Insert Unique Values Only Using MERGE

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


ADVERTISEMENT

Transact SQL :: Merge With Unique Identifier As PK

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

Transact SQL :: Efficient Way To Calculate Unique Values?

Jul 14, 2015

I have a data set as -

ID       Set Date          DB Date
100     Null                 07/01/15
100     07/05/15         07/02/15
100     07/10/15        07/08/15

I want to able to get 2 unique dates

1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.

2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.

The table has such records for lot of different ID's.

View 29 Replies View Related

Transact SQL :: SELECT Unique Values In Single Column?

Jun 8, 2015

I have the following two tables...

tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress

I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.

View 4 Replies View Related

Transact SQL :: Insert On Merge Statement Not Working

Oct 7, 2015

In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
 
[code]...

Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

View 10 Replies View Related

Transact SQL :: How To Insert Values From One Table To Another

Sep 17, 2015

I am trying to insert values from a temp table #TableName to a db table.

CATEGORY table- CategoryId, CategoryName, CategoryDescription

Then I did this :
Select CategoryName 
into #TableName 
from CATEGORY

Now I'm doing this :
Insert into #TestTable values(1,'This is:'+CategoryName)
select CategoryName from #Test 
CategoryID right now is not PK.

So, the intention is to insert as many CategoryNames available into TestTable with id 1 and category name edited as shown in Inset statement.

What is wrong with this query. Its not working.

View 12 Replies View Related

Transact SQL :: Join Same Table And Insert Values In Different Columns

Aug 7, 2015

I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....

declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')

[code]....

View 4 Replies View Related

Transact SQL :: How To Insert Dynamic Column Values Of A Table To Variables

Jul 18, 2015

I am trying to insert different number of columns into variables.  This is what it does If I use a static columns.

declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)

#MonthtoDate  temp table is created using a dynamic pivot query. 

Data looks like this :

Account Type  1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13

Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3]  from #MonthtoDate 

However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .

We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .

View 6 Replies View Related

Transact SQL :: Insert Using Select Intersect / Except - Number Of Supplied Values Not Match Table Definition

May 12, 2015

I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created

CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)

The following stmt won't work...

INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C

... as the number of supplied values does not match table definition.

View 2 Replies View Related

Lookup / Merge Join / Script - Howto Look Up Values By Comparing To A Range Of Values?

Jun 4, 2007

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:



"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _

" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _

" AND street_suff = @street_suff AND street_post = @street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"



My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value



Any suggestions?



thanks for your time...

View 5 Replies View Related

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 View Related

Merge Replication And Unique Contraints

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

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Merge Replication: Unique Index Error 1505

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

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

Transact SQL :: CTE To Peg Char Values To Int Values In Radio-style Chart

Aug 26, 2015

I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements?  I've updated my query as below. Although this sample query works, it's not working for my real data.  Instead, I get an error.  At the bottom is the error part of my real query.I copied all of the tables from the first query block below.  But when I wrote the bottom query block, it underlined in red the words "answer" and "question."  It says "Invalid column name". 

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')

[code]...

View 13 Replies View Related

PC SQL Server Table (with A Column Classified Unique) Synchronizes (using Merge) With 2 PPCs SQL Mobile Adding The Same Record

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

Inserting Unique Values

Apr 24, 2001

What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.

Suggestions are much appreciated.

Ivan

View 2 Replies View Related

Unique Values Between Two Tables.

Jul 20, 2005

Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.

View 2 Replies View Related

Unique Values Query

Apr 21, 2006

Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.

Thanks

View 5 Replies View Related

Transact SQL :: Set Unique Random Number

Dec 1, 2015

Lets say we have a table (tblProducts)

ID   Item             RandomNumber
-------------------------------------------
1    JEANS                      1234567
2    SHIRT                    72813550
3    HOOD                             Null
4    TROUSER               72191839
5    BLAZER                              0

I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query

select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts

So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.

View 15 Replies View Related

Transact SQL :: Unique Pairs Of Data?

Sep 23, 2015

In my table, I have some duplicate rows. I want to select only the unique value pairs.

So, the table/data look like this:

ColumnA  -- ColumnB
123  -- 234
234  -- 345
234  -- 345
345  -- 456

I want to create a select statement that will only return:

123  -- 234
234  -- 345
345  -- 456

In other words, the duplicate row i.e. the second 234 - 345 -- won't be included in the set my SELECT statement will return.

View 2 Replies View Related

Transact SQL :: Need Of Primary Key If Everything Can Be Achieved By Unique Key?

Oct 13, 2015

What is the need of Primary Key if everything can be achieved by Unique Key? Everything that primary key does, Unique key can also fill those things.Why primary key is required?

View 4 Replies View Related

Get Unique Values In A Group Statement

Jul 23, 2006

Hi,
Suppose a table [Inventory]:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:


Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis Perrakis

View 10 Replies View Related

Finding All Unique Combinations Of Values?

Mar 29, 2013

how to find all possible combinations of values, for example:

My table includes:

Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);

I need the output to be like this:

apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry

I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.

View 5 Replies View Related

SQL 2012 :: Generate Unique Values

Apr 23, 2014

I am working on an ASP.net web application which inserts new record into an underlying table.

It is actually a ConfirmationNumber and should be unique. I have used abs(checksum(newid()))

For this purpose. Is there a better way to accomplice this?

View 9 Replies View Related

Return Unique Count (Two Values)

Sep 6, 2013

I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.

ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04

I need two results.

Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.

View 3 Replies View Related

Analysis :: How To Get Unique Values For Each Measure

Sep 2, 2015

I am getting same value all over how to get unique values for each measures.

how to resolve it.

View 5 Replies View Related

Help With A Join To Return Unique Values

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 7 Replies View Related

Finding UNIQUE Values In Only One Column

Jan 28, 2008

I am using Access 2007 and I have 10 columns of data.

I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.

I have used this

SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];

But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.

I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.

I know that I am missing something basic but well, I can't figure it out.

Thanks

Bob

View 4 Replies View Related

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

Transact SQL :: How To Get 8 Digit Unique Number In Server

May 19, 2015

How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.

Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'

Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved