SQL Bahubali's please help

[4Vikram]

Okay I have a situation here..

I have a staging table ABC in which I have two columns named A and B which i need to insert into my destination table called BBC

So for BBC I have to insert these two columns as well as they also have some columns which needs to be inserted and the values will be hard coded
with the known values.. two of the columns in BBC are ID's which simultaneously increases and some others have some defined names which will be hardcoded

So BBC has some mapping tables as well which which needs to be mapped with BBC so for this we have a sp called map_sp which is in a way that if any entry is made to BBC 
table will automatically make an entry into mapping tables as well.

So my query and requirement is..

I need a new sp which should be in a way that it should get the records from ABC table (only required columns) and in the same sp it should also call the mapping sp (map_sp) as
well.

My collegues suggested to use a while loop in new sp with creation of temp table and also declaring it. I am little confusing here as I never did anything like this before

Please experts help me will post the exact details also if wanted with my mapping sp also, let me know thanks....

[4Vikram]

andaru seeing no one helping,, fleez help man anyone

LTT

[rakeshchinna]

8 minutes ago, 4Vikram said:

andaru seeing no one helping,, fleez help man anyone

LTT

Is this not the case of recursive sp..

[BillMarkSteve]

post the code here....somebody can help you then...

[4Vikram]

6 minutes ago, rakeshchinna said:

Is this not the case of recursive sp..

No I dont think so.

[4Vikram]

3 minutes ago, BillMarkSteve said:

post the code here....somebody can help you then...

I dont have code man, but I can post the existing mapping tables sp which should be called in new sp... let me see

 

[argadorn]

Procedure lo multiple procedure pettali ...first insert statement rayi dani tahrvtha if ststment rayi paian insert success ayithay e procedure run cheyyi ani 

example:

create  procedure sp_(

declare teh variables

insert stements you can write procedure for 

if  ABOVE STATEMENT SUCCESS

CALL PROCEDURE 

END IF

above is not solution

[4Vikram]

this is mapping sp which when made an entry to main table will also make entries in mapping tables as well..

So this should be called in my new sp where what I have to do is pick two columns from my staging table and insert into the table named equipment list..

for the rest of the columns in equipment list the columns need to be hardcoded...

 

ALTER procedure [dbo].[usp_APP_ADD_EQUIPMENT_AND_MAP_SITE_CAT] 
(
    @EQUIP_CATEGORY_ID        bigint,
    @NEW_EQUIPMENT_NAME        varchar(200),
    @SITE_ID                bigint,    
    @MWC_Tag_Number            varchar(100),
    @by                        varchar(100)=null,
    @o_msg                    nvarchar (500)=null    OUTPUT    
)
as

 

set nocount on

 

    SELECT @NEW_EQUIPMENT_NAME = REPLACE(@NEW_EQUIPMENT_NAME,'''''','"')

    DECLARE @PIPE int, @EQUIPMENT_NAME varchar(200), @SAP_FUNC_LOC_CD varchar(200)

    --Remove pipe (|) symbol in @NEW_EQUIPMENT_NAME
    SET @PIPE = charindex('|',@NEW_EQUIPMENT_NAME,1)

    IF @PIPE > 0

        BEGIN    --WITH PIPE

            SET @EQUIPMENT_NAME = SUBSTRING(@NEW_EQUIPMENT_NAME,1,@PIPE-2)
            SET @SAP_FUNC_LOC_CD = SUBSTRING(@NEW_EQUIPMENT_NAME,@PIPE+2,100)

        END        --with pipe

    ELSE 

        BEGIN    --NO PIPE

            SET @EQUIPMENT_NAME = @NEW_EQUIPMENT_NAME

        END        --NO PIPE
    
    --Selected or entered equipment name should be within 200 character limit including 'NSAP-' part.
    IF(CharIndex('NSAP-', @EQUIPMENT_NAME) = 1) -- Equipment Name starts with 'NSAP-'
        BEGIN
            IF(LEN(REPLACE(@EQUIPMENT_NAME,'NSAP-','')) > 195)    -- 
                BEGIN                    
                    SET @IS_LENGTH_EXCEEDED = 1
                END
        END
    ELSE    -- Equipment Name does not starts with 'NSAP-'
        BEGIN
            IF(LEN(@EQUIPMENT_NAME) > 195)    -- Equipment Name not starting with 'NSAP-' should not be more than 40 character
                BEGIN                    
                    SET @IS_LENGTH_EXCEEDED = 1
                END            
        END
    
    

    --Equipment name is within limit
    IF(@IS_LENGTH_EXCEEDED = 0)            
        BEGIN
            IF NOT EXISTS (SELECT * FROM [dbo].[tbl_APP_LIST_EQUIPMENT] 
                            WHERE (LOWER(EQUIPMENT_NAME) = 'NSAP-'+LOWER(@EQUIPMENT_NAME) OR LOWER(EQUIPMENT_NAME) = LOWER(@EQUIPMENT_NAME))                                )
                BEGIN

                INSERT INTO [dbo].[tbl_APP_LIST_EQUIPMENT]
                               ([EQUIPMENT_NAME]
                                --,[MWC_TAG_NUMBER]
                               ,[RECORD_CREATED_BY]
                               ,[RECORD_CREATED_DATE]
                               ,[LAST_MODIFIED_BY]
                               ,[LAST_MODIFIED_DATE])
                    SELECT
                               'NSAP-' + @EQUIPMENT_NAME AS [EQUIPMENT_NAME]
                               --,'NAMITA TEST'--@MWC_Tag_Number
                               ,@BY AS [RECORD_CREATED_BY]
                               ,@getdate AS [RECORD_CREATED_DATE]
                               ,@BY AS [LAST_MODIFIED_BY]
                               ,@getdate AS [LAST_MODIFIED_DATE]

                END
            ELSE
                BEGIN
                    SET @IS_EXIST = 1
                END

        --CAPTURE EQUIPMENT-MWC_ID

            DECLARE @EQUIP_MWC_ID bigint            

            SELECT @EQUIP_MWC_ID = MWC_ID FROM [dbo].[tbl_APP_LIST_EQUIPMENT]
            WHERE (LOWER(EQUIPMENT_NAME) = 'NSAP-'+LOWER(@EQUIPMENT_NAME) OR LOWER(EQUIPMENT_NAME) = LOWER(@EQUIPMENT_NAME) AND (SAP_FUNC_LOC_CD = @SAP_FUNC_LOC_CD OR @SAP_FUNC_LOC_CD IS NULL))
        
        
--Update     [dbo].[tbl_APP_LIST_EQUIPMENT] // Workaround
        IF  EXISTS
         (SELECT MWC_ID FROM [dbo].[tbl_APP_LIST_EQUIPMENT]
            WHERE (LOWER(EQUIPMENT_NAME) = 'NSAP-'+LOWER(@EQUIPMENT_NAME) OR LOWER(EQUIPMENT_NAME) = LOWER(@EQUIPMENT_NAME) AND (SAP_FUNC_LOC_CD = @SAP_FUNC_LOC_CD OR @SAP_FUNC_LOC_CD IS NULL)))
        BEGIN
                
            UPDATE  [dbo].[tbl_APP_LIST_EQUIPMENT]
            set [MWC_TAG_NUMBER] = @MWC_Tag_Number
                WHERE MWC_ID = @EQUIP_MWC_ID     
        END
        --ACCOUNT FOR IN CAT-TO-EQUIP

            IF NOT EXISTS (SELECT * FROM [dbo].[tbl_APP_MAP_CATEGORIES_TO_EQUIPMENT] 
                            WHERE EQUIPMENT_ID = @EQUIP_MWC_ID AND
                                  [EQUIP_CATEGORY_ID]= @EQUIP_CATEGORY_ID)
                BEGIN

                    INSERT INTO [dbo].[tbl_APP_MAP_CATEGORIES_TO_EQUIPMENT]
                               ([EQUIP_CATEGORY_ID]
                               ,[EQUIPMENT_ID]
                               ,[RECORD_CREATED_BY]
                               ,[RECORD_CREATED_DATE]
                               ,[LAST_MODIFIED_BY]
                               ,[LAST_MODIFIED_DATE])
                    SELECT
                                @EQUIP_CATEGORY_ID AS [EQUIP_CATEGORY_ID]
                               ,@EQUIP_MWC_ID AS [EQUIPMENT_ID]
                               ,@BY AS [RECORD_CREATED_BY]
                               ,@getdate AS [RECORD_CREATED_DATE]
                               ,@BY AS [LAST_MODIFIED_BY]
                               ,@getdate AS [LAST_MODIFIED_DATE]

                END

            IF NOT EXISTS (SELECT * FROM [dbo].[tbl_APP_MAP_SITES_TO_EQUIPMENT] 
                            WHERE EQUIPMENT_ID = @EQUIP_MWC_ID AND
                                  SITE_ID= @SITE_ID)
                BEGIN

                    INSERT INTO [dbo].[tbl_APP_MAP_SITES_TO_EQUIPMENT]
                               ([SITE_ID]
                               ,[EQUIPMENT_ID]                       
                               ,[RECORD_CREATED_BY]
                               ,[RECORD_CREATED_DATE]
                               ,[LAST_MODIFIED_BY]
                               ,[LAST_MODIFIED_DATE])
                    SELECT
                                @SITE_ID AS [SITE_ID]
                               ,@EQUIP_MWC_ID AS [EQUIPMENT_ID]
                               ,@BY AS [RECORD_CREATED_BY]
                               ,@getdate AS [RECORD_CREATED_DATE]
                               ,@BY AS [LAST_MODIFIED_BY]
                               ,@getdate AS [LAST_MODIFIED_DATE]

                END
        END

    SELECT ISNULL((SELECT MWC_ID FROM [dbo].[tbl_APP_LIST_EQUIPMENT]    
                        WHERE ((LOWER(EQUIPMENT_NAME) = 'NSAP-'+LOWER(@EQUIPMENT_NAME)) 
                                    OR LOWER(EQUIPMENT_NAME) = LOWER(@EQUIPMENT_NAME)) 
                                        AND (SAP_FUNC_LOC_CD = @SAP_FUNC_LOC_CD OR @SAP_FUNC_LOC_CD IS NULL)),NULL) AS MWC_ID
           ,ISNULL((SELECT EQUIPMENT_NAME FROM [dbo].[tbl_APP_LIST_EQUIPMENT]    
                        WHERE ((LOWER(EQUIPMENT_NAME) = 'NSAP-'+LOWER(@EQUIPMENT_NAME)) 
                            OR LOWER(EQUIPMENT_NAME) = LOWER(@EQUIPMENT_NAME)) 
                                AND (SAP_FUNC_LOC_CD = @SAP_FUNC_LOC_CD OR @SAP_FUNC_LOC_CD IS NULL)),NULL) AS EQUIPMENT_NAME
           ,@IS_EXIST AS IS_EXIST
           ,@IS_LENGTH_EXCEEDED AS IS_LENGTH_EXCEEDED 

    --PROC PERFORMANCE CAPTURE
    
    SET @dur_secs = convert(float,getdate()-@getdate)
    EXEC usp_JOBS_LOGIC_EXECUTION_HISTORY_V2 @obj, @getdate, @dur_secs, 1, @hist_id

    

[4Vikram]

4 minutes ago, argadorn said:

Procedure lo multiple procedure pettali ...first insert statement rayi dani tahrvtha if ststment rayi paian insert success ayithay e procedure run cheyyi ani 

example:

create  procedure sp_(

declare teh variables

insert stements you can write procedure for 

if  ABOVE STATEMENT SUCCESS

CALL PROCEDURE 

END IF

above is not solution

ante staging nunchi columns ni temp ki pampi akada nunchi ikadiki insert cheskochu aa?

[Prabhas_Fan]

[Bhai]

1 hour ago, 4Vikram said:

Okay I have a situation here..

I have a staging table ABC in which I have two columns named A and B which i need to insert into my destination table called BBC

So for BBC I have to insert these two columns as well as they also have some columns which needs to be inserted and the values will be hard coded
with the known values.. two of the columns in BBC are ID's which simultaneously increases and some others have some defined names which will be hardcoded

So BBC has some mapping tables as well which which needs to be mapped with BBC so for this we have a sp called map_sp which is in a way that if any entry is made to BBC 
table will automatically make an entry into mapping tables as well.

So my query and requirement is..

I need a new sp which should be in a way that it should get the records from ABC table (only required columns) and in the same sp it should also call the mapping sp (map_sp) as
well.

My collegues suggested to use a while loop in new sp with creation of temp table and also declaring it. I am little confusing here as I never did anything like this before

Please experts help me will post the exact details also if wanted with my mapping sp also, let me know thanks....

where is cnn man

[4Vikram]

3 minutes ago, Bhai said:

where is cnn man

HERE

[krishnote3]

support pettuko bro..why this 

[jbourne]

10 minutes ago, krishnote3 said:

support pettuko bro..why this 

Ankul Akkada 29.5 yrs industry 

[mekapichal_mnthmkora]

1 hour ago, 4Vikram said:

Okay I have a situation here..

I have a staging table ABC in which I have two columns named A and B which i need to insert into my destination table called BBC

So for BBC I have to insert these two columns as well as they also have some columns which needs to be inserted and the values will be hard coded
with the known values.. two of the columns in BBC are ID's which simultaneously increases and some others have some defined names which will be hardcoded

So BBC has some mapping tables as well which which needs to be mapped with BBC so for this we have a sp called map_sp which is in a way that if any entry is made to BBC 
table will automatically make an entry into mapping tables as well.

So my query and requirement is..

I need a new sp which should be in a way that it should get the records from ABC table (only required columns) and in the same sp it should also call the mapping sp (map_sp) as
well.

My collegues suggested to use a while loop in new sp with creation of temp table and also declaring it. I am little confusing here as I never did anything like this before

Please experts help me will post the exact details also if wanted with my mapping sp also, let me know thanks....