Hi all,
I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion.
Thank U.
Hi,
I'd really appreciate advice on now to avoid displaying duplicates in a resultset like the one below, only displaying the first field once and all the rows that correspond to that field all listed listed below. Distinct doesn't work. Thanks
Customer Name Address
============= =======
Big Bob's Books 123 Stone Lane
456 Seaside Drive
789 Waterhouse Square
1234 Mystery Drive
5678 Simple St
Tiny Tim's Tools
Tiny Tim's Tools
I am doing some audit and i have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...
I am using SP_Who2 and sql server Audit for auditing all data happening on sql server databases and dumping them to tables Audit_DBAudit abd Audit_sp_who2 and from then i am trying to get data which is not repeating/duplicate ...
SELECT
A.ProgramName
,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement
FROM Audit_DBAudit as F
Join [Audit_sp_who2] AS a
on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))
INSERT
INTO [Table2Distinct]
([CLAIM_NUMBER]
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
I have a table named "AUTHOR" with columns (name, citizenship, birth_year). I want to select all available pairs of authors (e.g. author1 author2) WITHOUT the reversed pairs (e.g. author2 author1). I am allowed only to use select (maybe a select is in an other select command).
Can anyone help me please??
I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?
View 6 Replies View RelatedI have a table T1 with columns: Date, Salesman, Sales. Salesman is a userid for each person and Sales is a decimal capturing sales done on Date.
From the data in T1 I would like to have a table with the following:
Salesman Sales-To-Date Sales-Last-Week
I have been able to do it with a temporary table, temp, as follows:
select Salesman, Sum(Sales) as ToDate into temp from T1 group by Salesman
with T2 as (
select Salesman, Lastweek=sum(Sales) from T1 where week>='1/28/2008' and week<='2/3/2008' group by Salesman
) select temp.Salesman, ToDate, Lastweek from temp full join T2 on temp.Salesman=T2.Salesman
How can I do the above in one query statement?
Thanks
Hi all,
Below are my tables:
Rowid
Name
1
John
2
Peter
3
Jack Table
Rowid
Rowid1
1
1
1
2
1
3
2
1
2
2
3
2
3
3 Table1
Rowid1
Country
1
USA
2
UK
3
JAPAN Table2
I tried to get the Country for all the people in the first table.
My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1
My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?
Name
Country
John
USA
UK
JAPAN
Peter
USA
UK
Jack
UK
JAPAN
Any advice will much appreciated.
I need to remove duplicate data from around 25 tables. I want to use a while loop to go through all tables. If I list out all of the column names the query runs fine, but since there are 25 tables some with 50 plus columns I was hoping to use something like the following, which errors out because my sub queries return more than one result.
SELECT q.* from
(Select ROW_NUMBER() OVER ( Partition BY (SELECT [name] AS [Name] FROM syscolumns
WHERE id = (SELECT id FROM sysobjects
WHERE type = 'U'
AND [Name] = 'Orders')
)
Order by (select top 1 [name] AS [Name] FROM syscolumns
[Code] ....
We are running a shopping mall in Korea and got a database including a table
of 4 million product prices, which is to be updated hourly basis. Updating 4million
records requires at least 10 minutes to complete. During the update, our shopping mall
exposed to customers does not respond quickly in fact very very slowly
and we investigated and found out that many tables of SQL database during the update were being
locked. As you know, site speed is top priority. We studied and found out that there are
two ways to avoid having locked tables during update, those are "read uncommitted" and
"snapshot" using the following lines.
set transaction Isolation level read uncommitted
set transaction Isolation level snapshot
We tried numerous times the above two lines and still find our tables being locked during update
and our customers are being disappointed.
My questions:
1. Is it possible at all in view of "the state of the art" to avoid having locked tables during update of 4 million records ?
2. if it is possible, would you please teach me like I am the beginner of database studies?
For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).
Sincerely
Daeyeon Jo
hi all
I have been posting in VB.NET forum ,but i am new to this forum.
I am working on an application using vast amount of data . So we get data in flatfiles. We used to get data for two or three months in more than three or four flat files .Some time we try to load the same files more than once .So it leads to duplicates in tables
I heard that there is some settings in table , so that it will not allow the same data once it is imported .
if anybody knows about this please help me
thanks & regards
Hi
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!
I have 2 tables below:
Table 1:
Product No Quantity
A 1
B 2
C 3
Table 2:
Product No Grade Quantity
A Good
A Normal
A Bad
B Good
B Bad
C Good
C Normal
C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies View RelatedMy basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies View RelatedI'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies View RelatedHi All,I am working on Web Application which deals with history data forreports and keeping track of changes.Current Solution :1) For each Entity I am having a column TID (Tracking ID) whichkeep on increasing for an instance of Entity. (so history and presentdata in the same table) . It makes querying the data difficult.2) I am also using month year table for Managing data i.e<TableNameMMYYYY> for a given month and yearPurposed Soluion :1) Using a seperate table so that history and present data isplaced seperately .2) Store All data in one table <TableName>Please guide me on advantages and disadvantages you pin point in thetwo approaches.With warm regardsJatinder
View 5 Replies View RelatedHello,
I have the following query,
SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )
It's out put is as follow:
Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha
Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,
Group ID GroupName
0 Abc
1 Beta
2 Alpha
Hope I explained what I need to see in result pane.
Thanks,
Junior
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
View 1 Replies View RelatedI found some fragmented data pages in my database tables.
I have found out using DBCC Showcontig commands.
I want to remove data fragmentation.
My tables are replicated. Is it possible to remove fragmentation
without using export and import. Please help me in fixing this problem.
Is there any way to fix replication failure with out recreating it.
My replication has failed saying is timout error.error is could not connect
subscriber. But I can able to ping the subscriber and if I create a new publisher and subscriber it is working fine. But existing one is not working.
What might be the possible reason. We have recreated the replication. We droped the existing one and recreated it. It took around 14 hours to sync.
Is there any way to resync with out recreate publication. We are using transaction replication and merge replication.
Vani
Hi All,
Very Ad-hoc requirement I am having.
I've got one table with two columns.
Column Name Data Type
1) Id Integer Identity
2) RemDate DateTime
I've to write one SP/JOB in that there will be an integer input parameter @numofday.
Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.
This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.
Thanks in advance,
Jai
Happy new year to all! Now a question...
I added a new column to StagePayments table - Activity - which is supposed to end up being the same as the JobActivityID from the JobActivities table. Basically, I need to get JobActivities (JA) info and put it in the StagePayment (SP) column. Problem is there are duplicate JA/Descriptions and SP/Activities, so what I need is to take the first sequence SP/Activity and grab the first JA/JobActivityID that matches for a particular JobID. Then get the next one of each and so on and so on...
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Tables...
CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Comment] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[Activity] [varchar] (30) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[ActivityID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [PK__StagePayment__457442E6] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [DF__StagePayme__Paid__4668671F] DEFAULT (0) FOR [Paid]
GO
CREATE INDEX [IX_StagePayment] ON [dbo].[StagePayment]([JobID], [Sequence]) ON [PRIMARY]
GO
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CREATE TABLE [dbo].[JobActivities] (
[JobActivityID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Activity_Status] [char] (1) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[Description] [char] (30) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[PlanStartDate] [datetime] NULL ,
[PlanEndDate] [datetime] NULL ,
[ActEndDate] [datetime] NULL ,
[AmountDue] [decimal](10, 2) NOT NULL ,
[CanDelete] [bit] NOT NULL ,
[Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [PK_JobActivity] PRIMARY KEY CLUSTERED
(
[JobActivityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [DF_JobActivities_JobActivityID] DEFAULT (newid()) FOR [JobActivityID]
GO
CREATE INDEX [IX_JobActivity] ON [dbo].[JobActivities]([JobID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivity_1] ON [dbo].[JobActivities]([JobID], [Activity_Status]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivity_2] ON [dbo].[JobActivities]([JobID], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities] ON [dbo].[JobActivities]([JobID], [PlanEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [JobActivities53] ON [dbo].[JobActivities]([JobID], [Description], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [JobActivities50] ON [dbo].[JobActivities]([JobID], [Description], [PlanEndDate], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_6] ON [dbo].[JobActivities]([JobActivityID], [Activity_Status], [Description]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_1] ON [dbo].[JobActivities]([JobID], [Sequence], [Description], [JobActivityID]) ON [PRIMARY]
GO
CREATE INDEX [IX_JobActivities_2] ON [dbo].[JobActivities]([JobID], [Sequence], [ActEndDate]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobActivities] ADD
CONSTRAINT [FK_JobActivity_Job] FOREIGN KEY
(
[JobID]
) REFERENCES [dbo].[Jobs] (
[JobID]
) ON DELETE CASCADE
GO
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
My example for one single JobID...
In JA -
JobActivityID Seq Description
34969C69-FF70-4313-91CC-43921DB3D74D 18 FOLLOWUP
48ACFEEE-3349-4F16-83E0-58F9B19E28E5 16 INSTALL
22507D44-6D0D-42DE-A211-9C23FDFCD19D 5 PLOTPLAN
BA88D04E-EBAE-40DB-A2C9-F909463D7F22 1 THANKU
83C48207-895B-4775-A62D-07059D8DEB62 10 NOTCUST
DBB8DF00-E26B-4E6F-9482-08E8CFE1588D 11 ROOMORD
BF621E91-E819-4F84-B507-0AA644D5C3F6 0 DWNPAY
6F595880-59D9-4E55-845D-19B477E8B179 2 THANKU
86D0A650-3B72-47E1-BDC2-2CA177DC3D53 12 NOTCSTRM
B0ABCC4C-A626-41C2-890C-3B9580326774 13 ROOMREC
F131C6FF-A86C-4527-A580-60FF7D3F0164 19 1YRFLWUP
7132625C-8E8B-4748-9176-6F06E8D0F20F 17 ARCMNT
AE06A938-323B-46EA-BA11-7D17B0985ACC 15 24HRCALL
DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 14 24HRCALL
EBC15C77-95D5-4C42-AD88-861F9DD7688B 9 RECPERMT
97D3D755-4B6F-4564-842B-A06945AA8890 8 SUBPERMT
CAACAACA-3B97-41D5-9A4A-A4E3E963D0BF 6 SUBCAD
0C3CB2E0-F4E9-43CD-81E4-ACE9F4022033 3 PHONCALL
42D498BE-308A-413E-965D-ADE7A7A21B97 4 MEASURE
7654C5E3-BED5-4F78-ADC8-DD4E283ADDEE 7 RECCAD
In SP -
Seq Activity ActivityID
1 NULL
2 24HRCALL
3 24HRCALL
4 INSTALL
I need to get SP to end up looking like this -
Seq Activity ActivityID
1 NULL NULL
2 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A
3 24HRCALL AE06A938-323B-46EA-BA11-7D17B0985ACC
4 INSTALL 48ACFEEE-3349-4F16-83E0-58F9B19E28E5
I have tried various versions of this...
begin transaction
update StagePayment
set Activity = (Select J.JobActivityID
from JobActivities J (nolock)
inner join StagePayment SP (nolock) on J.JobID = SP.JobID and J.Description = SP.Activity)
where Activity is not NULL
way too basic as I get this error...
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I know somehow I have to get the duplicate Activities in the same sequence as the Descriptions, but that's where I'm stuck?!?
If anyone is able to offer suggestions on how to get this to update correctly, I'd be very happy to hear about it!
Thanks in advance!
Tiffanie
Hi,
I need to query a database for a recordset and insert this into another database row-by-row.
For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MM:SS.0000 so for each inserted record the last decimal point would increment by 1.
Can I do this without a cursor ?
Any additional info I will provide gladly.
Thanks!
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:
Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?
Any advise would be much appreciated. Thanks!
Hi All,I want to avoid using cursors and loops in stored procedures.Please suggest alternate solutions with example (if possible).Any suggestion in these regards will be appreciated.Thanks in advance,T.S.Negi
View 22 Replies View Relatedi'm trying to write a batch that will perform a complex task usingset-based selects instead of a row-based cursor. let me know if you canhelp me figure out how.description of what i'm trying to do:there is TABLE1, TABLE2, and TABLE3i want to select each row from TABLE1, do some analysis on the data ofthat row, and then perform an insert of some data into TABLE2, and somedata into TABLE3how do i do this in a T-SQL batch?
View 9 Replies View RelatedApplication is a Work Tracking/Timesheet database.The increments of work are stored in the TimesheetItem table. Thiscontains, inter alia, the Work Code, the Start and the Duration thatthe employee spent that day on a particular project.Some employees in the Network Support Department don't complete astandard 7.5 hour day for various reasons, so for every Network Supportperson I need to update these particular days with an amount to bringthe total day's hours to 7.5.This SQL will get me a list of all TimesheetItem records for thepersonnel concerned.SELECTTimesheetItem.TypeID,[Work].WorkCode,TimesheetItem.Start AS Start,SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS HoursFROMTimesheetItem LEFT OUTER JOIN[Work] ON TimesheetItem.WorkID = [Work].WorkIDWHERE(TimesheetItem.EmployeeID IN(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))GROUP BYTimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCodeHAVING(TimesheetItem.Start >= @FromDate) AND (TimesheetItem.Start <= @ToDate)ORDER BYTimesheetItem.StartWhat I need is to group these records by EmployeeID where theaccumulated hours per day are < 7.5, so that I can then insert anincrement to make up the difference.I'm writing this from home and I don't have access to the tables toprovide a script, but there's nothing untoward there.Hope someone can help!ThanksEdward
View 10 Replies View RelatedI have a long sql batch that does this:1. Gets a list of all tables in user database that start with name COREdeclare@tablenamevarchar(30),@commandvarchar(2000),@cntintegerdeclare GetCOREOids cursor forselect sysobjects.namefrom sysobjectswhere ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and( sysobjects.name like 'CORE%' or sysobjects.name ='CMNSTRStructGeomBasicPort') and( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.namenot like 'COREDeletedObjects%') and( sysobjects.name not in('CORERelationOrigin','CORERelationDestination') ) and( sysobjects.id in ( select id from syscolumns where name = 'oid') )for read only2. Populates a temporary table with distinct oids from the list.create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)open GetCOREOidsfetch GetCOREOids into @tablenamewhile ( @@fetch_status = 0)beginselect @command = 'insert into [#tSP3DCoreOid] select distinct oid from' + @tablenameexecute(@command)--print @tablename + ' rows: ' + convert(char,@@rowcount)fetch GetCOREOids into @tablenameend /* while */--Clean upclose GetCOREOidsdeallocate GetCOREOids3. Creates a cursor to get "invalid" oids from 2 other tablesdeclare DanglingRelation cursor forselect oid from [dbo].[CORERelationOrigin] RO where not exists( select oid from [#tSP3DCoreOid] where oid = RO.oid )unionselect oid from [dbo].[CORERelationDestination] RD where not exists( select oid from [#tSP3DCoreOid] where oid = RD.oid )4. Loops thru. the cursor examining each oid and then calls aStoredProc to update another tabledeclare @objectOid uniqueidentifierdeclare @tempOid uniqueidentifieropen DanglingRelationfetch DanglingRelation into @ObjectOidwhile ( @@fetch_status = 0)beginset @tempOid='00000000-0000-0000-0000-000000000000'if left(@ObjectOid,8)='00000002'select @tempOid=oid from COREToDoList where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000003'select @tempOid=oid from COREToDoRecord where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000004'elseselect @tempOid=oid from COREBaseClass where Oid=@ObjectOidif @tempOid = '00000000-0000-0000-0000-000000000000'BEGINexec CORESetObjectIntegrity @ObjectOid, 2ENDfetch DanglingRelation into @ObjectOidendclose DanglingRelationdeallocate DanglingRelationdrop table [#tSP3DCoreOid]Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5million rows and this is making the batch fail on SQLServer as it runsout of memory.I think the problem is the cursor here and would like somefeedback/tips on how to best optimise it.thanks a lotSunit
View 4 Replies View Related
hi all,
i have a huge database and i am using Cursor to retrieve data and with each fatched data i m doing some operation....as my database is increasing the time duration to execute the cycle is also increation hugely....how to solve it??
thanks,
Hi, all!
Having a table with a varchar(10) column, I try to insert a row where the data for that column is more than 10 characters long. No surprise, I get a MSG 8152 error.
What I want is to get the overlong data clipped to the maximum column width, and no error. Is that possible?
Hi everyone,
As first task I've got Data Flow which loads a set of data into a .NET recordset.
After that, inmediately flow execution goes to For..Each Loop. I'd like to avoid go in that direction when Data Flow returns zero rows.
How can I do such thing?
I've tried this on Precedence Constraint Editor but it doesn't work..It doesn't recognize EOF keyword..
@[User::ResulSet] == EOF
Thanks in advance for your input,
Enric