T-SQL (SS2K8) :: Reading A Control Table - Avoid Using A Cursor
Jan 21, 2015
I am trying to think of a way to read a control table, build the SQL statement for each line, and then execute them all, without using a cursor.
To make it simple... control table would look like this:
CREATE TABLE [dbo].[Control_Table](
[Server_Name] [varchar](50) NOT NULL,
[Database_Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
[Code] ....
So if we then load:
insert into zt_Planning_Models_Plant_Include_Control_Table
values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
Then you would build a SQL script that would end up looking like the following (note all the columns are the same):
insert into master_models
Select * from r2d2.planning1.dbo.models
insert into master_models
select * from r2d2.planning7.dbo.models
insert into master_models
Select * from deathstar.planning3.dbo.models
View 3 Replies
ADVERTISEMENT
May 6, 2015
DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
View 1 Replies
View Related
Jun 4, 2015
I have a cte:
With cte as
(
Select distinct Incident_ID,
ACTUAL_SEVERITY ,
Policy
From
table)
There are 3 ACTUAL_SEVERITY value: 1-High, 2-Medium and 3-Low
I need the final result be like:
Policy High Medium/Low
How do I write the script with out using temp table or cursor?
View 6 Replies
View Related
Jan 13, 2006
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
View 2 Replies
View Related
Sep 28, 2006
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!
View 9 Replies
View Related
Jul 23, 2005
Application 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 Related
Oct 25, 2007
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,
View 5 Replies
View Related
Oct 16, 1998
I defined a stored procedure with a cursor inside for updating data.
When I call it from an MSAccess client, it fails.
When I execute it directly in a ISQL/w windows, it doesn`t fail but it displays me the data (wich is the reason for failing from MSAccess).
Do somebody know if I could do it without displaying data in the screen ??
View 2 Replies
View Related
Mar 20, 2015
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress,
tabela, tax, taxamaxima,[evactual],
[evaldepact],[ereintact],nrregbt,[taxAmtAno]
FROM deprec
ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex:
Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT
SET @cnt=0
WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
View 9 Replies
View Related
Aug 20, 2013
I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records.building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.
scripts (sample):
CREATE TABLE [dbo].[tbl_PersonDetails](
[PersonID] [int] NOT NULL,
[LeaveTimeId] [int] NOT NULL
) ON [PRIMARY]
[code]...
Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :
1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails
2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails.
3. not using LEFT join
View 9 Replies
View Related
Jun 4, 2015
How we can avoid blocking and deadlock?
View 3 Replies
View Related
Jan 3, 2015
rewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?
Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),
[Code] ....
--> In this table AccountID is uniquee
--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)
-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates
SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1
[code]....
View 9 Replies
View Related
Jan 27, 2008
Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
With Thanks
M.Mahendra
View 5 Replies
View Related
Jun 13, 2014
I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;
[code]...
View 8 Replies
View Related
Mar 27, 2015
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
View 8 Replies
View Related
May 8, 2014
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
View 6 Replies
View Related
Sep 9, 2014
The below cursor is giving an error
DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)
DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Why does the delete statement give an error ?
View 3 Replies
View Related
Feb 3, 2015
i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .
create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp
[code]....
how to include cursor on if part and else part
View 2 Replies
View Related
Jul 2, 2014
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
View 9 Replies
View Related
Aug 9, 2014
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
View 8 Replies
View Related
Dec 23, 2014
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable
[Code]....
View 2 Replies
View Related
Sep 25, 2015
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
[code]....
View 5 Replies
View Related
Jun 26, 2014
I currently have a process that has a cursor. It takes data and executes a stored procedure with parameters.
declare @tmpmsg varchar(max)
declare @tmpmsgprefix varchar(max)
declare @cms varchar(20)
create table #tmpIntegrity(matternum varchar(10),ClientName varchar(20))
insert into #tmpIntegrity(matternum,ClientName)
[Code] ....
Output from code:
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4,
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','
View 5 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT Â
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
  [PSDCode]
, PV.ValuationEffectiveDate
  [EffectiveDate]
, PV.PropertyListAlterationDate
  [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
View 11 Replies
View Related
Nov 2, 2015
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?
View 5 Replies
View Related
Sep 25, 2007
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
View 1 Replies
View Related
Sep 29, 2006
I have following query to delete the data from fact history table based on fact table. logid, level and post_date uniquely identify the rows on both fact and history table. I want to create indexes on the joined columns.I tried with clustered index (logid, level and post_date) it gives clustered index scan. I also tried with non clustered indexes on each column (logid, level and post_date) but still getting table scan.
Do you have any suggestion on which columns should I create proper indexes to avoid table or index scan? There are about 6 million rows on each table.
DELETE xbar_fact_history
FROM xbar_fact_history AS a
INNER JOIN xbar_fact AS b
ON a.logid = b.logid
AND a.level = b.level
AND a.post_date = b.post_date
AND a.check_CheckSum <> BINARY_CHECKSUM(b.out_mins,b.nor_hrs,b.pdi_call)
______________________________________________________________
Thanks
Sanjeev
View 5 Replies
View Related
Jul 23, 2005
To any and all;I have a very large table (16Mil+ records), for which I want to deleteabout 8 Million records. There are a few indexes on this table.Is there a way that I can run either a query or a series of queriesthat will run against each record and delete based on criteria (date)?If I do a single DELETE query, it will take forever, lock the table,and my app that runs against it will stop INSERTING, which is bad.If I do a cursor, I think it locks the table also, so that won't do,right?Any help would be appreciated.Glenn DekhayserContentcatcher.com
View 6 Replies
View Related
May 26, 2008
Anyone see a way to trick the optimizer into not scanning all tables involved in the view?
-- create two test tables
create table dbo.test1
(testID int, TestName varchar(10))
create table dbo.test2
(testID int, TestName varchar(10))
-- populate
declare @i int
set @i = 1000
while @i > 0
begin
insert into dbo.test1
select @i, '1.' + cast(@i as varchar(5))
set @i = @i - 1
end
insert into dbo.test2
select 1, '2.1' union all
select 2, '2.2'
go
-- create view
create view dbo.vw_Test
as
select1 as QueryID,
TestName
fromdbo.Test1
union all
select2 as QueryID,
TestName
fromdbo.Test2;
go
-- this works as i want, only scans table dbo.Test2
select *
fromdbo.vw_Test
whereQueryId = 2
-- joining to a table triggers scan of both tables in view:
declare @table table (QueryID int)
insert into @table
select 2;
selectvt.TestName
fromdbo.vw_Test vt
join@table t on
vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
Nathan Skerl
View 6 Replies
View Related
Apr 8, 2005
I receive in a table a field which is an xml string
like below
<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</NewOrder>
Is there a way read this field with a select statement? I need to strip out the entryid value in a trigger
View 4 Replies
View Related
Aug 23, 2006
-- drop proc SP_Trio_Popul_Stg_Tbls1
CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
-- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
declare @sql varchar (20)
set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last)
SELECT nm_name FROM dbo.' + quotename(@tableName)
print @sql
exec (@sql)
----------------------------------------------------------------------
exec SP_Trio_Popul_Stg_Tbls1 '00485'
----------------------------------------------------------------------
INSERT INTO dbo.Name
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Name'.
im trying to run this SP but im getting an error. Can you help me to fix it?
-------------------------
gongxia649
-------------------------
View 20 Replies
View Related
Jul 13, 2007
I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1]
AS
-- For brevity, I made it as simple statement.
SELECT *
From DB2.dbo.table2
GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can€™t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
Thanks in advance for your help
With regards
Ganesh
View 5 Replies
View Related
Feb 16, 2008
I need to select all the records in a table, loop through them one by one, calculating some new field data, and then write the new data back to the same table. Here is the basic structure of what I've come up with:<CODE>SqlCmd = SqlConn.CreateCommandSqlStatement = "SELECT ProductID, Name FROM tblProducts"SqlCmd.CommandText = SqlStatementSqlRdr = SqlCmd.ExecuteReaderIf SqlRdr.HasRows Then While SqlRdr.Read If SqlRdr.FieldCount > 0 Then ... SqlWriteCmd = SqlConn.CreateCommand SqlStatement = "UPDATE tblProducts SET Name = '" & NewName & "' WHERE ProductID = " & CStr(ProductID) SqlWriteCmd.CommandText = SqlStatement SqlWriteCmd.ExecuteNonQuery() SqlWriteCmd = Nothing End If End WhileEnd IfSqlRdr.Close()SqlCmd = Nothing </CODE>I get an error that tells me to close out the Reader before trying to execute the write query. But I can't close it out for the loop to work properly. So I assume that there must be another way to do this simple task, but I'm so new to all of this that I need some help! Thanks!
View 1 Replies
View Related