I have been given a job of taking two tables (trimmed down create scripts below) and merging their data into a third table.
CREATE TABLE dbo.Ztest_nominal ( BMCode varchar(20) NOT NULL, nomcode varchar(500) NOT NULL, Description nvarchar(4000) NULL, LanguageID int NOT NULL, CONSTRAINT PK_Ztest_nominal PRIMARY KEY CLUSTERED (BMCode ASC) ) CREATE TABLE dbo.Ztest_Participation ( ParticipationID int IDENTITY(1,1) NOT NULL, BMCode varchar(20) NOT NULL, LatestVersionNo varchar(5) NOT NULL, LastVersionSubmitted varchar(5) NOT NULL, CONSTRAINT PK_Ztest_Participation PRIMARY KEY CLUSTERED (BMCode ASC) )
The third tables columns to take the contents of above (basically the above two tables):
[ParticipationID] [int] IDENTITY(1,1) NOT NULL, [BMCode] [varchar](20) NOT NULL, [LatestVersionNo] [varchar](5) NOT NULL, [LastVersionSubmitted] [varchar](5) NOT NULL, [nomcode] [varchar](500) NOT NULL, [Description] [nvarchar](4000) NULL, [LanguageID] [int] NOT NULL
Ztest_nominal has about 63,000,000 rows and
Ztest_Participation about 62,000
The sql I was going to use (with an insert into) is (I did have the inner select outside before I gave up and posted here!):
SELECT p.ParticipationId, p.LatestVersionNo, n.* FROM Ztest_nominal n INNER JOIN ( SELECT ParticipationId, LatestVersionNo, BMCode FROM Ztest_participation WHERE LatestVersionNo = LastVersionSubmitted ) p ON p.BMCode = n.BMCode
However i feel its going need some optimisation so its not slow, also I've already had out of memory errors when running it.
Any help would be appreciated