SQL 2012 :: Deleting Repeated Records In Table
Sep 24, 2015
I have one table having three columns.This table contains lot of repeated records. I want to delete this records .
In this below example i want to delete all the records which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
First three records the columns id and no contains same value. I want to delete this three records.
But in last record for id =35 and no column =45623.it is not repeated so it should not be deleted.
View 8 Replies
ADVERTISEMENT
May 26, 2015
I have this table:
CREATE TABLE [dbo].[ACT_SECUNDARIA](
[CODACTIVIDADE] [int] IDENTITY(1,1) NOT NULL,
[CODCTB] [int] NOT NULL,
[CODCAE] [int] NULL,
[CODSECTOR] [int] NOT NULL,
[Code] ....
I want to delete every record that has more than one entry (codctb; codcae)
For example: if there are three records with the same codctb and codcae I want to delete two so that there can only be one.
How can I achieve this using t-sql?
View 2 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Dec 2, 2007
Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks
View 5 Replies
View Related
Jul 6, 2006
I loaded one table via SSIS and found that it contained many duplicate records (from the input source). I can create a SQL task to delete them, but I wonder if SSIS offers and task "out of the box" to delete dups?
TAI,
barkingdog
View 6 Replies
View Related
Aug 24, 2015
I'm trying to delete some records from some tables in a SQL Server 2008 R2 database. There's a foreign key relationship between the two tables. To make things easier here's the definition of both tables:
-- Parent table
CREATE TABLE [dbo].[PharmInvInItemPackages](
[InventoryInDetailID] [int] IDENTITY(1,1) NOT NULL,
[InventoryInID] [int] NOT NULL,
[ItemPackageID] [int] NOT NULL,
[code]....
View 5 Replies
View Related
Jan 27, 2006
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works)
Public Function DelAll()
MZKDB = MZKHRFin
If sloption = "L" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;"
ElseIf sloption = "S" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";"
End If
sqlConn.Open()
sqlTrans = sqlConn.BeginTransaction()
sqlCmd.Connection = sqlConn
sqlCmd.Transaction = sqlTrans
Try
sqlCmd.CommandText = sqlStr
sqlCmd.ExecuteNonQuery()
sqlTrans.Commit()
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf
SetCursor()
Catch e As Exception
Try
sqlTrans.Rollback()
Catch ex As SqlException
If Not sqlTrans.Connection Is Nothing Then
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf
SetCursor()
End If
End Try
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf
SetCursor()
Finally
sqlConn.Close()
End Try
ResetID()
End Function
If cbGenFY.Checked Then
sqlStr = "DELETE FROM FIN_FiscalYear"
TableName = "dbo.FIN_FiscalYear"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFY()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFund.Checked Then
sqlStr = "DELETE FROM FIN_Fund"
TableName = "dbo.FIN_Fund"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFund()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFunc.Checked Then
sqlStr = "DELETE FROM FIN_Function"
TableName = "dbo.FIN_Function"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFunc()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenObject.Checked Then
sqlStr = "DELETE FROM FIN_Object"
TableName = "dbo.FIN_Object"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenObject()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenCenter.Checked Then
sqlStr = "DELETE FROM FIN_Center"
TableName = "dbo.FIN_Center"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenCenter()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenProject.Checked Then
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "dbo.FIN_CodeBook"
DelAll()
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "dbo.FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "dbo.FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_Project"
TableName = "dbo.FIN_Project"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProject()
timeStepStop = Date.Now
TableName = "dbo.FIN_Project"
DispOneCounts()
End If
If cbGenProgram.Checked Then
sqlStr = "DELETE FROM FIN_Program"
TableName = "dbo.FIN_Program"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProgram()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenGL.Checked Then
sqlStr = "DELETE FROM FIN_gl"
TableName = "FIN_gl"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenGL()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenRevenue.Checked Then
sqlStr = "DELETE FROM FIN_Revenue"
TableName = "FIN_Revenue"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenRevenue()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenBank.Checked Then
sqlStr = "DELETE FROM FIN_VendorBankAccnt"
TableName = "dbo.FIN_VendorBankAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_VendorBank"
TableName = "dbo.FIN_VendorBank"
DelAll()
sqlStr = "DELETE FROM FIN_bankAdd"
TableName = "dbo.FIN_bankAdd"
DelAll()
sqlStr = "DELETE FROM FIN_bankTERMS"
TableName = "dbo.FIN_bankTerms"
DelAll()
sqlStr = "DELETE FROM FIN_bank"
TableName = "dbo.FIN_bank"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenBank()
timeStepStop = Date.Now
TableName2 = "dbo.FIN_bankTERMS"
TableName3 = "dbo.FIN_BankAdd"
TableName4 = "dbo.FIN_VendorBank"
TableName5 = "dbo.FIN_VendorBankAccnt"
DispTwoCounts()
End If
If cbFinAP.Checked Then
sqlStr = "DELETE FROM FIN_Period"
TableName = "FIN_Period"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenPeriod()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbFinVM.Checked Then
sqlStr = "DELETE FROM FIN_vendorClass"
TableName = "FIN_vendorClass"
DelAll()
sqlStr = "DELETE FROM FIN_vendorAdd"
TableName = "FIN_vendorAdd"
DelAll()
sqlStr = "DELETE FROM FIN_vendor"
TableName = "FIN_vendor"
DelAll()
sqlStr = "DELETE FROM FIN_AddressType"
TableName = "FIN_AddressType"
DelAll()
sqlStr = "DELETE FROM FIN_VendorStatus"
TableName = "FIN_VendorStatus"
DelAll()
sqlStr = "DELETE FROM States"
TableName = "States"
DelAll()
sqlStr = "DELETE FROM Country"
TableName = "Country"
sqlStr = "DELETE FROM FIN_IndustrialCodes"
TableName = "FIN_IndustrialCodes"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenIndCodes()
timeStepStop = Date.Now
DispOneCounts()
DelAll()
ClearCounts()
timeStepStart = Date.Now
FinVendStat()
timeStepStop = Date.Now
TableName = "FIN_VendorStatus"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinAddrType()
timeStepStop = Date.Now
TableName = "FIN_AddressType"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenCountry()
timeStepStop = Date.Now
TableName = "Country"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenState()
timeStepStop = Date.Now
TableName = "States"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinVM()
timeStepStop = Date.Now
TableName = "FIN_Vendor"
TableName2 = "FIN_VendorAdd"
DispTwoCounts()
End If
If cbFinbudget.Checked Then
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "FIN_CodeBook"
DelAll()
ClearCounts()
TableName = "FIN_Budget"
timeStepStart = Date.Now
FinBudget()
timeStepStop = Date.Now
DispOneCounts()
ClearCounts()
TableName = "FIN_Codebook"
TableName2 = "FIN_budgetAccnt"
timeStepStart = Date.Now
FinCodeBook()
timeStepStop = Date.Now
DispTwoCounts()
ClearCounts()
End If
View 4 Replies
View Related
Sep 11, 2007
Hi,
I have a query that is executing properly but i want to delete the results of the query. I am trying to do it but i am messing up somewhere in the syntax
Can anybody help me out with this problem?
Below is the query
DELETE FROM DPT_NEW_BINS WHERE(
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_temp_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX))
CAN SOMEBODY POINT OUT MY MISTAKE?
View 3 Replies
View Related
May 24, 2007
No transaction log involved, only the table itself.
Use sp_spaceused "table_name" to check the space used.
It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.
Recovery mode set to be simple, autoshrink turned on.
The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.
Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue
Thanks!
View 2 Replies
View Related
Aug 5, 2015
I have many large tables with millions of records in a SQL Server database. They all use an Identity column which is the clustered index. We haven't been deleting any records until recently because disk space is now becoming a problem.
Assuming I delete a lot of old records, I'm thinking that the freed up space in the data pages won't be reused. New records will be added at the end of the table because of the Identity column being the clustered index. So the table will keep getting bigger even though there is lots of free space.
Assuming I'm right, then how do I recapture this unused space? Is an alter table rebuild the best way? Or rebuilding the clustered index?
View 2 Replies
View Related
Feb 21, 2007
Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
AS
SELECT Product.ProductID, Title
FROM Product INNER JOIN
(ProductCategory INNER JOIN
(Category INNER JOIN Department ON Department.DepartmentID = Category.DepartmentID)
ON ProductCategory.CategoryID = Category.CategoryID)
ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
View 2 Replies
View Related
Mar 5, 2014
finding the solution for the below query?It displays repeated records.
select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from
[code]....
View 2 Replies
View Related
Mar 13, 2014
Is this close to the correct syntax for a stored procedure for deleting all the data from a particular table... or is there a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TruncateTmpBank
[Code] ....
View 3 Replies
View Related
May 20, 2014
I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.
My estimated execution plan shows a index scan.
Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?
View 3 Replies
View Related
Nov 3, 2015
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U
[Code] ....
View 1 Replies
View Related
Sep 21, 2015
I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
View 8 Replies
View Related
Sep 4, 2007
I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word
Table containing the records like below
ID
Movie Name
New Name
1
Spider Man
Spider Man
2
Spider Man 2
Spider Man
3
Spider Man 3
Spider Man
4
Spider Man UK
Spider Man
5
Spider Man USA
Spider Man
6
New Spider Man
Spider Man
7
Spider Man Black
Spider Man
8
Spider Man Part 1
Spider Man
9
Spider Man Part 2
Spider Man
10
Spider Man I
Spider Man
11
Spider Man III
Spider Man
12
Spider Man Part II
Spider Man
My manufacturer send me the data in this format and i have to allocate there new name
to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a repeated records along with suggestion
as new name.
I am fully confident that you guys will help me out from this problem.
Looking forward
View 9 Replies
View Related
Aug 26, 2014
I'm trying to come up with a query for this data:
CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
[Code] ....
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
[Code] ....
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits
View 3 Replies
View Related
Aug 14, 2014
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
View 0 Replies
View Related
Jun 12, 2015
I have a requirement to delete 1 Million records from a table having 10 Million data and it's being queried on 24/7 basis (don't have a downtime). how can I achieve that?
View 13 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Sep 14, 2007
Hi:
I am fairly new to SQL Server 2005 and before now, I have only had to restore databases, and connect to tables via ODBC connection in a reference (read only) setup. Today, I have a very small project to set up using the server.
I have a userlogon.csv file that the network stores on a file server in a hidden share logon$. It has 4 columns, UserID, Computer, Date, Time.
I was able to create a database called UserLogon and import the file as it was today. I want to create a scheduled update so the server would go to this file perhaps 4 times a day (or more) and grab any new logins that have appended itself to this CSV file.
So, as a newbie with a 1,900 page SQL Server 2005 unleashed manual at my side, could someone outline what the steps are in general I should follow to set this up?
I have the process laid out in my mind, but I don't know how to translate in into a scheduled task of the SQL Server :
1. Create DB and import the table (done)
2. create a stored procedure that connects to the CSV file and evaluates date and time stamps then appends any new records into the SQL db table. (appending records would be achieved by using the INSERT and WHERE statements?)
3. Schedule a job to perform this task on a routine basis.
It appears that the file connection portion of this set up is defined outside the evaluation and append record procedure? (not in the same stored procedure). Perhaps I tie the whole process together using the Job Manager, selecting the file settings, and then the stored procedure to be performed on the file.?
I hope I have been descriptive enough to ask if someone could outline the modules/features/processes involved so I can read up on them and figure them out using the book.
Thank you in advance.
David
View 1 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jul 8, 2014
What I need to know is how to get repeated data from a existing table, to normalized tables with out typing it...
Example:
---------------------
| ID |TEXT | TAGS |
---------------------
| 1 |text1|repeating|
| 2 |text2|repeating|
----------------------
How to normalization would be use 3 tables ,one for tags_id and tags, and another relating the tags_id to ID from the table above...but how does the code already know there is a tag called repeating?
View 3 Replies
View Related
Mar 25, 2014
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
View 3 Replies
View Related
Sep 23, 2014
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies
View Related
Jul 12, 2004
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?
View 10 Replies
View Related
Feb 25, 2005
I have a table with a load of orphaned records (I know... poor design)
I'm trying to get rid of them, but I'm having a brain cramp.
I need to delete all the records from the table "Floor_Stock" that
would be returned by this select statement:
SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT =
FLOOR_STOCK.PRODUCT LEFT OUTER JOIN
BOD_HEADER ON FLOOR_STOCK.PRODUCT =
BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE =
BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))
I was thinking along the lines of:
DELETE FROM FLOOR_STOCK INNER JOIN
(SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT =
FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT =
BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON
FLOOR_STOCK. PRODUCT = F. PRODUCT
AND FLOOR_STOCK.SITE = F.SITE
... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
View 14 Replies
View Related
Mar 3, 2004
How do I delete one record from one table and cascade down all related tables?
Mike B
View 14 Replies
View Related
Aug 14, 2006
Hey all,
Here is the scenario. I'm working with two tables:
Contact1
Conthist
Contact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record.
The link between the two tables is a column called accountno.
I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno.
Any help would be appreciated.
Thanks,
Tony
View 4 Replies
View Related
Apr 12, 2007
Whenever I try to delete records from a table joined to another table, like so:
DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
I get the error:
Major Error 0x80040E14, Minor Error 25501
> DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = FROM ]
This is an SQL CE database, and SQL Server Management Studio. Any ideas?
View 1 Replies
View Related
May 18, 2006
I have a couple SQL tables that have been appended to daily over the last two years. There is now about 50,000,000 records in the table. Does anyone know the fastest way to delete records before a certain date to shorten these tables? Delete queries and everything else I've tried is taking way too long.
View 13 Replies
View Related
