Hi,
i have created index for a temporary table and this script should used by multiusers.So when second user connecting to it is giving index i mean object already exists.
So what i need is when the second user connected the script should create one more index on temporary table.Will sql server provide any random way of creating indexes if the index exists already with that name??
Thank You,
Hi,How can I create a temporary table say "Tblabc" with column fieldsShmCoy char(2)ShmAcno char(10)ShmName1 varchar(60)ShmName2 varchar(60)and fill the table from the data extracted from the statement..."select logdetail from shractivitylog"The above query returns single value field the data seperated with a '·'Ex:BR··Light Blue Duck··in this case I should getShmCoy = 'BR'ShmAcno = ''ShmName1 = 'Light Blue Duck'ShmName2 = ''I want to do this job with single SQL query. Is it possible. Pls help.Herewith I am providing the sample dataBR··Light Blue Duck···0234578···BR··Aqua Duck···0234586···UB··Aqua Duck··Regards,Omav
View 1 Replies View RelatedHello,
I am working on a webapp using VB.net
Right now I am writing to a sql table during a process where the end user
starts entering the contents for a file that is going to be generated once he
finishes entering the data, but the problem is that if more than one user is
doing the same process the data would get mixed up. To avoid this I
thought in creating a temporary table (its name will consist of a string
and the current date time).
I would like to see any tutorial
about creating and working with temp tables. Or if you have any
suggestions, I will appreciate them. Thanks
HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal
View 4 Replies View RelatedUsing SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedEnvironment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0
I was doing a code review for another developer and came across this code:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
[Code] ....
This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
[code]...
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID
[Code] .....
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Code] ....
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
Hi
i am new to using this sql server 2000....this is a very simple question to all u guys.....i am just in a learning stage...so any help from u guys is really appreciable....
i need to create a table customers with the following columns...
identity column to self-populate as the primary key,
joindate, leavedate, custcode, empID.
This is the one i tried:
create table customers (id int primary key identity (1,1) not null,
joindate smalldatetime null,
leavedate smalldatetime null,
custcode varchar (10) not null,
empid int not null
)
is tht code correct only???
and i also want the below one :
Create indexes on the leavedate, custcode and empid columns.
how to create these indexes???
and wht happens when i create them(like is thr any advantage of creating indexes???)
thanks......
I have a table called as A table :
CREATE TABLE [dbo].[A](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [ProID] [int] NOT NULL,
 [LID] [varchar](12) NOT NULL,
 [EventID] [varchar](12) NOT NULL,
 [HEventID] [varchar](12) NULL,Â
) ON [PRIMARY]Â Â Â
How I should creating the appropiate index for this table?AÂ few option that I think ok.
Opt 1 : creating a primary key on the autoID with create index . create non clustered index on ProID and EventID
Opt 2 : create a primary key on the autoID with non clustered index . create clustred index on ProID and EventID .
opt 3: create primary key on the ProID and EventID with clustered index.
I have read thru the article on the primary key, clustered and non clustered indexing. However when I want to applyied the indexing..I feel a bit lost.But among the 3 option.... what is the different..
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
Hi,
I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.
Do I create the triggers to maintain indexes myself?
Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?
Would it be "Alter index <index name> REBUILD command?
Appreciate the help.
Gulden
I am trying to create a sample table in the Azure SQL  Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
[Code] ....
what's the correct syntax
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Hi All,
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows:
1.PerformanceDeficiencyNotice
2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below
“Incorrect syntax near the keyword ‘INTO’�.
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT
pdn.transactionid,
pdn.employeenbr,
pdn.lastname,
pdn.firstname,
pdn.processlevel,
pl.facilityname as processlevelname,
pdn.department,
pdn.jobcode,
pdn.title,
pdn.supemployeenbr,
pdn.managername,
pdn.timeframe as pdn_timeframe,
pdn.actualeffectivedate as pdn_startdate,
/*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/
/*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/
case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then
case pdn.timeframe
when '30' then dateadd(month,1,pdn.actualeffectivedate)
when '60' then dateadd(month,2,pdn.actualeffectivedate)
when '90' then dateadd(month,3,pdn.actualeffectivedate)
else null
end
end as pdn_enddate,
pdn.status as pdn_status,
status.description as pdn_statusdesc,
pdn.managersignoff as pdn_managersignoff,
pdn.managersignoffdate as pdn_managersignoffdate,
pdn.associatesignoff as pdn_associatesignoff,
pdn.associatesignoffdate as pdn_associatesignoffdate,
pdn.witnessname as pdn_witnessname,
/*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/
/*where the evaluationtype = 'X' (Extension Final).*/
/*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/
case
when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate)
else null
end as ext_startdate,
eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/
eval.evaluationtype as eval_evaluationtype,
evaltype.description as eval_evaltypedesc,
eval.status as eval_status,
status2.description as eval_statusdesc,
eval.effectivedate as eval_effectivedate,
eval.managersignoff as eval_managersignoff,
eval.managersignoffdate as eval_managersignoffdate,
eval.associatesignoff as eval_associatesignoff,
eval.associatesignoffdate as eval_associatesignoffdate,
eval.witnessname as eval_witnessname
into #pdninfo
FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval
on pdn.transactionid = eval.transactionid
left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl
on pdn.processlevel = pl.processlevel
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status
on pdn.status = status.status and status.type = 'PDN'
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2
on eval.status = status2.status and status2.type = 'EVAL'
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype
on eval.evaluationtype = evaltype.type
/*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/
WHERE pdn.status in ('A','S')
/*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/
/*'D' - In Progress, or 'N' - Not started)*/
OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/
/*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/
/* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/
/* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/
SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate
into #perfreview
FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview
WHERE #pdninfo.employeenbr = perfreview.employeenbr
AND perfreview.updatedate =
(SELECT max(updatedate)
FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2
WHERE perfreview2.employeenbr = perfreview.employeenbr
AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/
SELECT 'orig' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/
SELECT
'ext' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
drop table #pdninfo
drop table #perfreview
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedI have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
I am creating an index on a table wit 35 million records but I get the error
'TT_ObjPerformance' table- Unable to create index 'IX_TT_ObjPerformance_CACode'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
How can I get the index created?
ThanksSQL Server newbie
Hi,
Trying to optimize a query but not sure what to do. I have this query on which I ran an exec plan,
SET NOCOUNT ON;
SELECT qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion, qaTests.TestMachine, qaTestSuite.TestClientMachine, qaTests.TestLogin, qaTests.TestLabel,
qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTests.TestID, qaTests.TestIDInternal, qaTests.TestStart,
qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled, qaTests.TestWFBTime,
qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd, qaTestMssgs.TestMssgsID,
qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage, qaTestSuite.TestMode
FROM qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID INNER JOIN
qaTestMssgs with(NOLOCK) ON qaTests.TestID = qaTestMssgs.TestID
order by qaTestSuite.TestSuiteStart DESC
and it gives me the following results:
Use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index.
Physical Op: Bookmark Lookup
Logical Op: Bookmark Lookup
Est. Row Count: 128
Est. Row Size: 4760
Est. I/O Cost: 0.368
Est. CPU Cost: 0.000141
Est. Execs: 1.0
Est. Cost: 0.368888(89%)
Est. Subtree Cost:.415
Argument:
BOOKMARK:([Bmk1004]), OBJECT:([QAMaster].[dbo].[qaTestMssgs]) WITH PREFETCH
I have no idea what to do with that. Anyone have any clues? What I found online was that I should make a Covering Index, but I didn't find
any patterns on how to do that. Any one have ideas of how to do this?
Thanks very much for your help!
--PhB
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
View 3 Replies View RelatedI need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
Hi
I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database
This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end
IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end
Than you.
Hi
I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.
thanks
asm
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales | -20,000.00 | 01 | 2005
===================================================
1000| Sales | -10,000.00 | 02 | 2005
===================================================
1001| Cost | 5,000.00 | 01 | 2005
===================================================
1001| Cost | 5,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date
=========================================================
Sales | 20,000.00 | 10,000.00 | 30,000.00
Total Sales | 20,000.00 | 10,000.00 | 30,000.00
Cost | 5,000.00 | 5,000.00 | 10,000.00
Total Cost | 5,000.00 | 5,000.00 | 10,000.00
=========================================================
Profit | 15,000.00 | 5,000.00 | 20,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
How would someone set up a temporary table from a database in order to then run a query against it to get more accurate data??
View 11 Replies View RelatedHi,
i have:
[first table]
user_id
user_name
user_password
[second table]
set
item
order
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
thank you for your thought!
Hi,
my code looks like this
Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp
Declare @results VarChar(100)
Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '')
From @Temp
where id_customer = 12
Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
thank you
hi!
i used some temporary table in store procedure (sqlserver 2005)
our team have report software calisto .
the calisto use crystal and reports which use
this store procedure .
because of that,
we have list of many temporary table with the same name
#dbo.sug_name ... ,#dbo.sug_name ... ,......
in the system database .
what could be the reason for that and how can we drop it ?
Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#sug_name', because it does not exist or you do not have permission."
Hi all
Is it possible to name a table when a script runs with the date ie
select name, address
into test (date)
from company
where the (date) is will be todays date
Thanks
Richard
Hi
I call some SQL server procedures via OLEDB under Visual C++.
[ Commands like " EXEC myProc 'param1', 'param2' " ]
My connection string is Provider=SQLOLEDB;Data Source=.SQLEXPRESS;Integrated Security=SSPI;Initial File Name=C:devsrcindb.mdf
I encounter a pb when there is a 'SELECT (...) INTO #table' into my db procedure.
Something like:
ALTER PROCEDURE myProc
AS
SELECT 1 as 'return1', 2 as 'return2' into #table
SELECT * FROM #table
RETURN
works of course very well when I call it from Visual or Server Management Studio Express
but doesn't return any recordset when I call it from OLEDB.
2nd thing:
SELECT 1 as 'return1', 2 as 'return2' into table
SELECT * from table
-> table is created [I can see it in the db], but no selection is returned to my OLEDB caller.
Is it a security pb ?