SQL Server 2008 :: MERGE Statement - Cannot Filter Set Of Rows In Target Table

Feb 10, 2015

I have a table:

declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)

A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).

I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.

If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:

merge ( select * from tableName where id = 10 ) as target
using ...

and I can't have:

merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...

This means I cannot filter the set of rows in the target table to a handful of records where id = 10.

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: MERGE And Delete From Target

Mar 12, 2015

I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.

My current code is

sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,

[Code] ....

The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.

My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.

Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.

If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like

NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.

That's quite a bit of work my end to do that tho so I'd like to be sure it works.

View 1 Replies View Related

Transact SQL :: MERGE Statement Did Not Catch Existing Row In Target Table

Nov 8, 2015

I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table.  It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions. 

If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance? 

Below is the error message:

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).

The statement has been terminated.

DDL of the target table:

CREATE
 TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,

[Code] ....

View 8 Replies View Related

SQL Server 2008 :: Merge Statement When Source Table Supplies Duplicates

Apr 26, 2015

With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.

DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,

[code]....

View 0 Replies View Related

DB Design :: Optimizing Merge Statement By The Use Of Indexes On Target

Nov 13, 2015

This website describes how Merge statements should be optimized by the use of indexes on the target?source tables: [URL]..... It says that a clustered index should be created on the join column in the target and a unique covering index on the source table. 

I have read in other articles that insert/delete/update statements perform worse on tables with clustered indexes as the leaf level pages will have to be reorganized.

Why in the case of Merge statement having a indexes actually improve the performance of insert/delete/update statements?

View 3 Replies View Related

SQL Server 2008 :: Snapshot Replication Of 1 Table To A Target DB With 100 Tables

Apr 25, 2015

Regarding Snapshot replication....

If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....

Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?

View 2 Replies View Related

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

Feb 19, 2015

I need the requirements of merging multiple rows of same ID as single row.

My Table Data:

IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL

Required Output Should be,

IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH

How to achieve this output. Tried grouping but its not working also producing the same result.

View 1 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

T-SQL (SS2K8) :: MERGE Insert Not Working Into Target Table

Mar 16, 2014

I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.

MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE

[Code] .....

View 8 Replies View Related

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

Jun 20, 2013

Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?

I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.

Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.

Server Configuration:

SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM

SQL Code:

Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;

[code]....

View 9 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.

E.G.

A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:

'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'

What would be the best way to do this ?

View 3 Replies View Related

SQL Server 2008 :: Replicating Merge Range And File Drops For Partitioned Table?

Jul 28, 2015

I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.

Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.

There the following article options:

Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes

I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.

I could not find and option to copy partition functions.

View 0 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2008 :: Delete Duplicate Rows From ANY Table

Jul 31, 2015

Is there is 'fairly' simple query to delete duplicate rows from ANY table ?

A script which removes duplicates from a table, when a table_name is supplied.

View 9 Replies View Related

SQL Server 2008 :: Check For Missing Rows In A Table

Oct 20, 2015

I have a table of languages, identified by a lang_id column as follows:

LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech

I also have a RESOURCES table of phrases (for translation purposes) similar to this:

res_id res_lang res_phrase
AccessDenied en Access Denied

For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.

For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.

View 6 Replies View Related

SQL Server 2008 :: Return All Rows From Any Table Containing Value Range In Specified Column?

Sep 3, 2015

I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.

I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.

View 9 Replies View Related

SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date

Mar 5, 2015

I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.

Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.

View 2 Replies View Related

SQL Server 2008 :: Deleting Data (rows) From Table To Reclaim Space?

Feb 11, 2015

I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.

If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?

View 8 Replies View Related

SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table

Apr 29, 2015

How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now

View 4 Replies View Related

SQL Server 2008 :: Add Target Specific Extended Properties To Replication Subscriber Tables

Oct 6, 2015

I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?

View 0 Replies View Related

SQL 2012 :: Complex Filter For Server Merge Replication

Jul 26, 2015

We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:

Id int identity,
ParentId int,
GroupId int

Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.

A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child

The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.

Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child

And the filter would look like this:

WHERE GroupId = 200

However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…

View 2 Replies View Related

SQL Server 2008 :: Store Long Values To Be Used In (IN) Statement In Separate Table?

Jul 14, 2015

I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.

What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.

View 9 Replies View Related

Parent/Child Rows In Report, Nested Table, Textbox Value In Filter Condition

Mar 26, 2008

Hi All,

I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.

I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?


I want to display parent/child records on report. I am not getting the proper solution.

The data is like this:

Sequence ItemCode IsParent

1 XYZ 0 'do not have child record

2 PQR 1 'have child records with sequence no 3

3 ASD 0

3 AFDGE 0

3 VDC 0

4 ASR 1 'have child records with sequence no 5
5 ASR 0

If IsParent = 1, that record has child records with sequence = parent sequenece + 1



I think u can understand the data I need to bind, and it is like:

XYZ

+ PQR

ASD

AFDGE

VDC

ASR

On + click we can do show/hide of child records.

I m not getting how to achive this in SQL server report. Can u give some hint?

Thanks in advance
Pravin

View 1 Replies View Related

Merge Two Rows In The Same Table

Jul 23, 2005

I want to create a stored procedure that will merge columns from tworows that contain duplicated contacts.I have can easily identify the duplicates and extract the UniqueIDs asparameters, but I can't figure out how to construct the actual updateSQL.@KeeperID int,@DupeID intUpdate ContactsSETa.Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),a.Info2 = LEFT(TRIM(IsNull(a.info2,'') + ' ' IsNull(b.Info2,'')),255),etc, etc...FROM(here's what I can't figure out)Contacts a ID = @KeeperIDContacts b ID = @DupeID

View 2 Replies View Related

SQL Server 2008 :: SSIS - Download Files From FTP Using Filter

Feb 8, 2015

I need to use SSIS to connect to an FTP server. From there I need to download files to a local folder. I need to download only today's files and also on those files starting with Training or Recruitment. I have managed to set up tasks that copy all but I am having such a hard time writing a script using C# that will download using the filters.

View 2 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL Server 2008 :: Filter List Of Employees Where Sum Of VALIDATED Hours Is Less Than 80?

Mar 12, 2015

How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.

Here is the flat table

SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO

Output will be something like this

12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG

Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.

View 2 Replies View Related

INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Apr 23, 2008



Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.

Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.

What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination

RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.

The package is run thru BIDS manually so there is no sucurity restrictions attached to it.

I am not sure what I am missing. And I do not see error in my package either. It is not failing.

Thanks in advance!



View 6 Replies View Related

Merge Allmost Identical Rows For Output Table

Dec 20, 2007

I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.

View 3 Replies View Related

Does The Actual Update Statement Of A Table Affect Merge Replication?

Dec 8, 2006

Suppose your using Merge Replication.

2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed.

Will this break Merge Replications conflict tracking? Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns.

View 1 Replies View Related

SQL Server 2012 :: Filter MS Objects Out Of Select Statement

Jul 27, 2015

I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:

dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents

View 2 Replies View Related

SQL Server 2008 :: Merge 2 Select Statements

Aug 25, 2015

SELECT
part.num, woitem.qtytarget AS woitemqty,

(SELECT LIST(wo.num, ',')

FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID

[Code] ...

View 5 Replies View Related







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