question

SrinivasMaddula-4047 avatar image
0 Votes"
SrinivasMaddula-4047 asked MelissaMa-msft commented

How to Select and insert only the records that are approved

Hi All,

I've to transfer the data from one Line table to another Line table based on the "Doc_Status" present in the Header Table.

Example:

I've 4 records in EMP_Header but only 2 records with EMP_ID: 2 & 4 are Approved(Doc_Status), So I need to copy only the lines of 2 records with EMP_ID: 2 & 4.

Below is My code, please enhance my code.

INSERT INTO [dbo].[EMP_Test_Header]
([EMP_ID]
,[EMP_Name]
,[DOC_Status]
,[Gender]
,[Int_Flag])

SELECT emp.[EMP_ID]
,emp.[EMP_Name]
,emp.[DOC_Status]
,emp.[Gender]
,emp.[Int_Flag]
FROM [EMP_Header] emp
where emp.[DOC_STATUS] = 'Approved' and emp.[Int_Flag] = 'C'

INSERT INTO [dbo].[EMP_Test_Line]
([EMP_ID]
,[Line_No]
,[Subject]
,[Marks]
,[Int_Flag])

SELECT line.[EMP_ID]
,line.[Line_No]
,line.[Subject]
,line.[Marks]
,line.[Int_Flag]
FROM [EMP_Line] line
where line.[Int_Flag] = 'C'

Thanks in Advance,
Srinivas Maddula.

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

please enhance my code.

"Enhance" in which way; you didn't mentioned that?

0 Votes 0 ·

Hi @SrinivasMaddula-4047,

Could you please provide any update?

Thank you!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SrinivasMaddula-4047,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Based on limited information you provided, please have a try with below and check whether it is helpful.

 INSERT INTO [dbo].[EMP_Test_Line]
 ([EMP_ID]
 ,[Line_No]
 ,[Subject]
 ,[Marks]
 ,[Int_Flag])
 SELECT line.[EMP_ID]
 ,line.[Line_No]
 ,line.[Subject]
 ,line.[Marks]
 ,line.[Int_Flag]
 FROM [EMP_Line] line 
 left join [EMP_Header] emp on line.[EMP_ID]=emp.[EMP_ID]
 where emp.[DOC_STATUS] = 'Approved' and emp.[Int_Flag] = 'C' and line.[Int_Flag] = 'C'

OR

 INSERT INTO [dbo].[EMP_Test_Line]
 ([EMP_ID]
 ,[Line_No]
 ,[Subject]
 ,[Marks]
 ,[Int_Flag])
 SELECT line.[EMP_ID]
 ,line.[Line_No]
 ,line.[Subject]
 ,line.[Marks]
 ,line.[Int_Flag]
 FROM [EMP_Line] line 
 where exists (select 1 from  [EMP_Header] emp where line.[EMP_ID]=emp.[EMP_ID] and emp.[DOC_STATUS] = 'Approved' and emp.[Int_Flag] = 'C')
  and line.[Int_Flag] = 'C'

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.