SQL query taking a long time to execute

USE Pooja
GO
----Create TestTable
CREATE TABLE TestTable(RtJobCode VARCHAR(20), RtProfCode smallint,RtTestCode smallint,ProfCode smallint,TestCode smallint)
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (RtJobCode, RtProfCode,RtTestCode,ProfCode,TestCode)
SELECT RtJobCode,RtTestCode,TestCode,RtProfCode,ProfCode
FROM dbo.ResultTest,dbo.Test,dbo.Profiles
WHERE RtTestCode=ANY(Select TestCode from dbo.Test)

----Verify that Data in TestTable
SELECT *
FROM TestTable

GO

The above code tries to take out entries from a table called resutltest and profiles and test,

The problem was during creation of a cube i was encountering some data which was not consistent in all the tables, So i tried a join on the tables but as the tables contained a huge number of columns it was'nt feasible so tried making this code which just keeps on executing without stopping and not displaying any data

Resulttest's Rtte开发者_高级运维stcode is foreign key from testcode

Your query is very slow because it is making a cartesian product between ResultTest, Test and Profiles. you need to provide "join" conditions to link the tables together.

SELECT RtJobCode
     , RtTestCode
     , TestCode
     , RtProfCode 
     , ProfCode
FROM dbo.ResultTest r 
JOIN dbo.Test t
  ON r.RtTestCode = t.TestCode
JOIN dbo.Profiles p
  ON r.RtProfCode = p.ProfCode 

I speculate that this is the query you are looking for. Note the conditions that link ResultTest and Test together and the condition that links ResultTest and Profiles together.

USE Pooja
GO
----Create TestTable
CREATE TABLE TestTable(RtJobCode VARCHAR(20), RtProfCode smallint,RtTestCode smallint,RtCenCode smallint,LabNo int,ProfCode smallint,ProfRate money,ProfName varchar(100),TestCode smallint,TestRate money,TestName varchar(100),TestCategory varchar(50),Cost money)
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (RtJobCode, RtProfCode,RtTestCode,RtCenCode,LabNo,ProfCode,ProfRate,ProfName,TestCode,TestRate,TestName,TestCategory,Cost)
SELECT RtJobCode
     , RtProfCode
     , RtTestCode
     , RtCenCode
     , LabNo
     , ProfCode
     , ProfRate
     , ProfName
     , TestCode
     , TestRate
     , TestName
     , TestCategory
     , Cost
FROM dbo.ResultTest

JOIN dbo.Test 
  ON ResultTest.RtTestCode = Test.TestCode
JOIN dbo.Profiles 
  ON ResultTest.RtProfCode = Profiles.ProfCode