How to insert a Lakh of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert a Lakh of records

Author

Topic

karthickraj.s
Starting Member

4 Posts

Posted - 2011-06-17 : 02:21:30

Hi all,
I have a records with one table i should insert a records with different conditions.If all the conditions are satisfy it should insert another table.
I am using the cursor.If i use the cursor it will take much more time and works in my local system only,not in online (Thread was being aborted).
Please help me to solve this issue.

Thanks in advance.

Regards,
Karthick

Thanks & Regards
Karthick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-17 : 02:35:17

Show us what you have so far so that we can understand the business logic as your post doesn't provide enough information for us to answer your question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

karthickraj.s
Starting Member

4 Posts

Posted - 2011-06-17 : 02:51:59

DECLARE
@getCursorInfo CURSOR,
@A_Imported_by VARCHAR(50),
@A_Imported_date DATETIME,
@A_Account_priority VARCHAR(50),
@A_Assigned_To VARCHAR(50),
@A_Assigned_by VARCHAR(50),
@A_Assigned_Date DATETIME,
@Status VARCHAR(50),
@A_CPROCID INT,
@ChkRecordNo INT,
@ChkPatientAccount VARCHAR(50),
@ChkClaimNo VARCHAR(50),
@ChkDOS VARCHAR(50),
@ChkDoctor_Code VARCHAR(50),
@ChkCPT VARCHAR(100),
@Temp_Record_no VARCHAR(10),
@Temp_ID_Record_no VARCHAR(10),
@Temp_Import_Id VARCHAR(10),
@Temp_ImportDetails_Id VARCHAR(10),
@Temp_Var VARCHAR(50),
@Temp_Patient_Account VARCHAR(50),
@Temp_Claim_No VARCHAR(50),
@Inventory_type INT

SELECT @Inventory_type=Inventory_ID FROM Client_Master WHERE Client_Id=@ClientId

SET @getCursorInfo = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
Record_No,Patient_Account,Claim_No,Doctor_Code,CPT,CONVERT(VARCHAR(11),DOS,101)
FROM
Temp_ImpportDetails
WHERE
Exist = 'Y' and Client_Id=@ClientId AND Imported_By=@Imported_By

OPEN @getCursorInfo
FETCH NEXT FROM @getCursorInfo INTO @ChkRecordNo,@ChkPatientAccount,@ChkClaimNo,@ChkDoctor_Code,@ChkCPT,@ChkDOS

WHILE @@FETCH_STATUS = 0
BEGIN
-- Inventory IF

IF(@Inventory_type=2)
BEGIN

-- IF condition --------------------

IF(NOT EXISTS( SELECT Import_ID
FROM ImportDetails
WHERE
(Client_Id=@ClientId) AND
(Imported_By=''+@Imported_By+'') AND
(Patient_Account=''+@ChkPatientAccount+'') AND
(Claim_No=@ChkClaimNo) AND
(CONVERT(VARCHAR,DOS,101) = ''+@ChkDOS+'')))

BEGIN
BEGIN TRY
BEGIN TRANSACTION

SELECT @m_ItemsList=''
SELECT @m_ItemsList=@m_ItemsList+ ARMS_FieldName + ',' FROM ImportFieldDetails_Master
WHERE (Client_id = @ClientId) AND (Temp_Id=@Temp_Id)

SET @FINAL = Left(@m_ItemsList,Len(@m_ItemsList)-1)

SET @FVALUES = 'SELECT TOP 1 '+@FINAL+',ID.Import_ID,ID.Record_no,ID.Client_Id,IM.Subsite_Id,IM.Imported_By,CURRENT_TIMESTAMP FROM Temp_ImpportDetails ID,ImportFile_Master IM WHERE IM.Import_id=id.Import_id AND ID.Exist=''Y'' AND ID.Imported_By='''+@Imported_By +''' AND ID.Patient_Account='+QUOTENAME(@ChkPatientAccount,'''') +' AND ID.Claim_No='''+@ChkClaimNo+''' AND (CONVERT(VARCHAR,ID.DOS,101) ='''+@ChkDOS+''')'
SET @QUERY = 'INSERT INTO ImportDetails ('+@FINAL+',Import_ID,Record_No,Client_Id,SubSite_Id,Imported_By,Imported_Date) ('+@FVALUES+')'
--PRINT(@QUERY)
EXEC(@QUERY)

SELECT @Temp_Import_Id=Import_ID,@Temp_Record_no=Record_no -- Getting Import_ID in Temp_ImpportDetails
FROM Temp_ImpportDetails
WHERE(Client_Id=@ClientId) AND
(Imported_By=''+@Imported_By+'') AND
(Patient_Account=@ChkPatientAccount) AND
(Claim_No=@ChkClaimNo) AND
(Exist ='Y') AND
(Record_No=@ChkRecordNo) AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')
ORDER BY Import_ID ASC

UPDATE ImportDetails
SET
PROCID=@Temp_Import_Id+'_'+CONVERT(VARCHAR(10),@ChkRecordNo) -- Updating the PROCID to Importdetails
WHERE (Client_Id=@ClientId) AND
(Imported_By=''+@Imported_By+'') AND
(Patient_Account=@ChkPatientAccount) AND
(Claim_No=@ChkClaimNo) AND
(Record_No=@ChkRecordNo) AND
(Import_ID=@Temp_Import_Id) AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

UPDATE Temp_ImpportDetails SET Exist='N' -- Updating the Exist status into "N"
WHERE
(Client_Id=@ClientId) AND
(Imported_By=''+@Imported_By+'') AND
(Patient_Account=@ChkPatientAccount) AND
(Claim_No=@ChkClaimNo) AND
(Exist ='Y') AND
(Record_No=@ChkRecordNo) AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
SELECT 'Fail'
END CATCH
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION

SELECT @m_ItemsList=''
SELECT @m_ItemsList=@m_ItemsList+ ARMS_FieldName + ',' FROM ImportFieldDetails_Master
WHERE (Client_id = @ClientId) AND Temp_Id=@Temp_Id

SELECT @FINAL = Left(@m_ItemsList,Len(@m_ItemsList)-1)
SET @FVALUES = 'SELECT TOP 1 '+@FINAL+',ID.Import_ID,ID.Record_no,ID.Client_Id,IM.Subsite_Id,IM.Imported_By,CURRENT_TIMESTAMP FROM Temp_ImpportDetails ID,ImportFile_Master IM WHERE IM.Import_id=id.Import_id AND ID.Exist=''Y'' AND ID.Imported_By='''+@Imported_By +''' AND ID.Patient_Account='''+@ChkPatientAccount+''' AND ID.Claim_No='''+@ChkClaimNo+''' AND (CONVERT(VARCHAR,ID.DOS,101) ='''+@ChkDOS+''')'

SET @QUERY = 'INSERT INTO ImportDetails ('+@FINAL+',Import_ID,Record_No,Client_Id,SubSite_Id,Imported_By,Imported_Date) ('+@FVALUES+')'
EXEC(@QUERY)

----------------------------------
UPDATE Temp_ImpportDetails SET [Exist]='N'
WHERE
Record_No = @ChkRecordNo AND
Patient_Account=@ChkPatientAccount AND
Claim_No=@ChkClaimNo AND
Client_Id= @ClientId AND
Imported_By=''+@Imported_By+'' AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

SELECT TOP 1 @Temp_Import_Id=Import_ID,
@Temp_Record_no=Record_No,
@Temp_Patient_Account=Patient_Account,
@Temp_Claim_No=Claim_No
FROM Temp_ImpportDetails
WHERE
[Exist]='N' AND
Record_No = @ChkRecordNo AND
Patient_Account=''+@ChkPatientAccount+'' AND
Claim_No=''+@ChkClaimNo+'' AND
Client_Id= @ClientId AND
Imported_By=''+@Imported_By+'' AND
(CONVERT(VARCHAR,DOS,101) =''+@ChkDOS+'')

DECLARE @QUERY22 VARCHAR(5000)
SET @QUERY22='UPDATE Temp_ImpportDetails SET [Exist]= ''D''
WHERE Import_ID='+@Temp_Import_Id +' AND Record_No ='+ @Temp_Record_no +' AND Patient_Account ='''+@Temp_Patient_Account+''' AND Claim_No='''+@Temp_Claim_No+''' AND (CONVERT(VARCHAR,DOS,101) ='''+@ChkDOS+''')'

EXEC(@QUERY22)

SELECT TOP(1)
@Temp_ImportDetails_Id=ID.Import_Id,
@Temp_Record_no=ID.Record_No,
@Temp_ID_Record_no=T_ID.Record_No
FROM ImportDetails ID,Temp_ImpportDetails T_ID
WHERE
(ID.Patient_Account=T_ID.Patient_Account) AND
(ID.Client_Id=T_ID.Client_Id) AND
(ID.Claim_No=T_ID.Claim_No) AND
(CONVERT(VARCHAR,T_ID.DOS,101)=CONVERT(VARCHAR,ID.DOS,101)) AND
(ID.Patient_Account=''+@ChkPatientAccount+'') AND
(ID.Client_Id= @ClientId) AND
(CONVERT(VARCHAR,ID.DOS,101) =''+@ChkDOS+'') AND
(ID.Imported_By=''+@Imported_By+'') AND
(ID.Forwarded='False') AND
(ID.ForwardedImportID IS NULL) AND
--(ID.Inherited='False') AND
(T_ID.[Exist]='D') AND
(T_ID.Record_No=@Temp_Record_no)

UPDATE ImportDetails
SET
Inherited='False',
Forwarded='True',
ForwardedImportID=@Temp_Import_Id,
Forwarded_RecordNo=@Temp_ID_Record_no
--PROCID=@Temp_ImportDetails_Id+'_'+CONVERT(VARCHAR(10),@ChkRecordNo)
WHERE
Record_No = @Temp_Record_no AND
Import_Id =@Temp_ImportDetails_Id AND
Patient_Account =''+@Temp_Patient_Account+'' AND
Claim_No=@Temp_Claim_No AND
Client_Id=@ClientId AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

UPDATE ImportDetails
SET
PROCID=@Temp_Import_Id+'_'+CONVERT(VARCHAR(10),@ChkRecordNo)
WHERE
Record_No = @ChkRecordNo AND
Import_Id =@Temp_Import_Id AND
Patient_Account =''+@Temp_Patient_Account+'' AND
Claim_No=@Temp_Claim_No AND
Client_Id=@ClientId AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

UPDATE ImportDetails
SET Inherited='True',
Forwarded='False'
WHERE
Import_ID=@Temp_Import_Id AND
Record_No = @Temp_ID_Record_no AND
Patient_Account =@Temp_Patient_Account AND
Claim_No=@Temp_Claim_No AND
Client_Id=@ClientId AND
(CONVERT(VARCHAR,DOS,101)=''+@ChkDOS+'')

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

-- END IF condition ------------------

END
END

-- Inventory END IF

FETCH NEXT FROM @getCursorInfo INTO @ChkRecordNo,@ChkPatientAccount,@ChkClaimNo,@ChkDoctor_Code,@ChkCPT,@ChkDOS
END

CLOSE @getCursorInfo
DEALLOCATE @getCursorInfo

Thanks & Regards
Karthick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources