I'm hoping someone can tell me how to construct a stored procedure that
deletes all records in tblA not matching the PK in tblB
This gives me the recordset of all records in tblA with no matching
records in tblB (ID is the PK in tblB)
SELECT a.ID
FROM dbo.tblB b
RIGHT OUTER JOIN dbo.tblA a ON b.ID = a.ID
WHERE
b.ID IS NULL
thanks,
lq
I have a table (tblA) that records the RecordID, UserID andLastViewedDate (DateTime) of each record opened in tblB where RecordIDis the PK in tblB. I want to construct a query that groups all recordsin tblA by RecordID, filters by UserID and keeps only the most recent25 RecordIDs and deletes the rest.This gets me a recordset of all RecordIDs filtered by UserID in tblAbut I can't figure out how to sort it by LastViewedDate DESC and toeliminate those not in the Top25:SELECT RecordIDFROM dbo.tblAWHERE (UserID = 1234)GROUP BY RecordIDAny help is appreciated!lq
View 4 Replies View RelatedI'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
Hi
I am very new to SSIS and currently was reading through various manuals but couldnt find a way to do a simple delete.
STAGE_ORDERS looks something like
order_id order_value
100 50000
300 70000
.....
Orders table looks like
order_id order_value
100 9000
200 1000
300 8000
now i want to use SSIS to delete the orders 100,300 which are present in staging table from the orders table .
How do i accomplish this task since i dont see a delete transformation.
How about updates if i want to update the order values from the staging table to the orders table what transformation can accomplish this
regards
Hrishy
Hi,
This is a where clause I am using in a search.
WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' )
I am trying to do a search which returns the most matching record. For example if I have a record with Denver as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return matching results in a ranked order.
Thanks
Hi,
I have the following table:
***********************************************************************************************
Sheet --- Cycle
Init SC --- 89
Post NCOA --- 89
Post Supp --- 89
Revised Final State Counts --- 89
Revised Final State Counts --- 94
***********************************************************************************************
Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?
Thanks
Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.
I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.
When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:
SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
The flat file destination was mainly used to confirm the select.
Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:
DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.
Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?
Thank you and God Bless.
There is something I don't understand. When I use join
SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
I am getting 76 orig_file records
But when I do
SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i right outer join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
I am showing only 8 records under i.orig_file column and I am not sure why. What I need is to get all the AP_INVOICEDOCS in the matching orig_file records.
Here is a very basic question that I have.
I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.
How can I pull back the records from table A that do not have a corresponding combination in B?
I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?
THANKS!
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
I have an 'update' query that looks like this:
update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
Hello,
I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.
I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.
So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.
TIA,
Kweri
Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.
Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.
Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?
Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?
Any help will be greatly appreciated.
Thanks,
Jojy
Hi Gurus!!!
I have two tables tabl_a and tbl_b
now tbl_a has some records which are not in tbl_b. I want to update tbl_b with records in tbl_a
eg:
tbl_a tbl_b
a a
b b
c c
d d
x
y
z
Now I want to update tbl_b with records 'x', 'y', 'z'. I want to keep the matching record just untouched.
Something similar.
How can I do that???
Thanks in advance!!!
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedHI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.
Category ID
Category Name
Category E-mail
1
Beverages
Beverages.com
2
Condiments
Condiments.com
3
Confections
Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottles
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jars
3
Chocolade
10 pkgs.
3
Gumbär Gummibärchen
100 - 250 g bags
3
Maxilaku
24 - 50 g pkgs.
3
Scottish Longbreads
10 boxes x 8 pieces
3
Sir Rodney's Scones
24 pkgs. x 4 pieces
3
Tarte au sucre
48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1), find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottlesFor Category Id: 2
Category ID
Product Name
Quantity Per Unit
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record? Any Help or sample URL, Tutorial Page will be appreciated. Thanks
I have an Excel file which contains some data. I want to load that into a SQL server Table. Here are my conditions :
1. If the table doesn't have any matching records from the Excel file, then my DFT should load the data from that Excel to the Dest Table.
2. If the table has even one or more matching records, then the DFT should not process at all, instead I should send an email to the business stating that there are some matching records and hence the package is not process...ed.
P.S. If i use Lookup, I have two matching and non-matching output. which will process the non matching records into the table and matching can be redirected to any flat/Excel file. But i don't want to do this. I just want to lookup the Sql Server table and excel.
It'll be good if there is an additional option in the Lookup "Fail component on matching records".
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime
Then i have some code like this: (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?
Patrick
Hello, I have 3 tables with their columns as follows: + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK + Labels [LabelId] PK , [LabelName] + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel
View 1 Replies View Relatedhello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?
cheers
Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right???
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help
Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc
Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)
objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()
Hi.
there are 300 records on the table.
the table has a field of "ID (autonumber)"
How can I delete records without latest 20 records?
There are 30 tables in my database and I need to delete all records within a date range. Could anyone help me?
View 4 Replies View RelatedHello,I have a database called articlesI want to delete all articles from a subgroup e.g. DVDIf I am in the enterprisemanager what steps do I have to take to do thisthanks in advancePaul
View 5 Replies View RelatedI have some records that will not delete, whenever I run a deletestatement in the Query analyzer, it never completes the statement, andI am only deleting one record at a time. Can anyone tell me why arecord wouldn't delete?
View 1 Replies View RelatedHi!
New on this
Is there a way on a sql sever where i can
delete som records one time every day
like if a field in my table is < getdate()
Alvin
Is there a way to delete all records in a SQL Server 2005 database? A function or a script?
View 8 Replies View RelatedI have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link.
If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A.
How do I write a query to delete ONE of the pair of duplicate records? I have two problems: -
Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.
Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates?
I need this done in SP.