locked
Insert Record RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below code and i want to insert record 1 by 1 in another temp table

    create table #temp( 
    		RowNo int not null IDENTITY (1,1) , 
    		[CardCode] varchar(15) not null
    	)
    	
    	DECLARE @RowCount int ;
    	DECLARE @i int = 1;
    	DECLARE @CardCode VARCHAR(15);
    	INSERT into #temp SELECT ([CardCode]) FROM [Pearl_Live].[dbo].[OCRD] where [CardType] = 'S'
        select @RowCount =  Count(*) FROM #temp 
    
    	while @i <= @RowCount 
    	BEGIN
    
    		Select @CardCode=CardCode From #temp Where RowNo = @i
    
    Here i want to loop thru another table where CardCode = @CardCode and insert record 1 by 1 in another table		
    
    		SET @i = @i + 1;
    	END

    Thanks

    Tuesday, November 12, 2019 8:37 AM

All replies

  • User753101303 posted

    Hi,

    It seems just about adding inside your loop a SQL insert statement ? Which problem do you have when  trying to add a statement such as :

    INSERT INTO YourTable(CardCode) VALUES (@CardCode)

    Also always double check you can't do this a single operration rather than using loops and cursors. It seems a single statement such as :

    INSERT INTO YourTable(CardCode) SELECT CardCode FROM [Pearl_Live].[dbo].[OCRD] where [CardType] = 'S'

    could be enough or be explicit about the problem you are trying to solve by doing a copy and then processing each line separately ???

    edit "I want to loop throguh another table". Are you sure you can't solve your problem by using a https://www.w3schools.com/sql/sql_join.asp clause to produce the resultset you need ?

    Tuesday, November 12, 2019 9:33 AM
  • User665608656 posted

    Hi jsshivalik,

    According to your description, you can follow this sql statement:

    create table #temp( 
    RowNo int not null IDENTITY (1,1) ,
    [CardCode] varchar(15) not null
    )

    DECLARE @RowCount int ;
    DECLARE @i int = 1;
    DECLARE @CardCode VARCHAR(15);
    INSERT into #temp SELECT ([CardCode]) FROM [Pearl_Live].[dbo].[OCRD] where [CardType] = 'S'
    select @RowCount = Count(*) FROM #temp while @i <= @RowCount BEGIN Select @CardCode=CardCode From #temp Where RowNo = @i; if exists (Select * from anothertable where CardCode=@CardCode) begin INSERT INTO anothertable(CardCode) VALUES (@CardCode) end SET @i = @i + 1; END

    Best Regards,

    YongQing.

    Tuesday, November 12, 2019 9:39 AM
  • User-797751191 posted

    Hi Yong

      In Inner statement there will be multiple records of Card Code and i want to insert record 1 by 1 since i have to perform some update on each row

    Thanks

    Tuesday, November 12, 2019 9:56 AM
  • User665608656 posted

    Hi jsshivalik,

    According to your description, is the statement you mentioned that returns multiple Card Code records that judges whether it exists through if?

    Do you want to add records based on the count obtained by the following statement?

    Select * from anothertable where CardCode=@CardCode

    If so, you can add a loop to execute under if judgment:

     create table #temp( 
    		RowNo int not null IDENTITY (1,1) , 
    		[CardCode] varchar(15) not null
    	)		
            DECLARE @RowCount int ;
    	DECLARE @RowSubCount int ;
    	DECLARE @i int = 1;
    	
    	DECLARE @CardCode VARCHAR(15);
    	INSERT into #temp SELECT (a.LastName) FROM  Employee a where a.FirstName = 'ddd'
            select @RowCount =  Count(*) FROM #temp 
    
    	while @i <= @RowCount 
    	BEGIN
    	        DECLARE @j int = 1;
    		Select @CardCode=CardCode From #temp Where RowNo = @i;
    
    		if exists (Select * from yourtable where  CardCode=@CardCode)
    		BEGIN
                     select @RowSubCount = Count(*) from yourtable where  CardCode=@CardCode
    		  WHILE @j <= @RowSubCount
    		  BEGIN
    		   INSERT INTO yourtable(CardCode) VALUES (@CardCode);
    		    SET @j= @j + 1;
                      END
    		END
    		SET @i = @i + 1;
    	END
    

    If I have a wrong understanding, please explain your needs clearly, and don't only provide part of the content, because we don't know your data structure and the relationship between tables.

    By the way, for questions, I hope you can describe your needs clearly and provide your data source as much as possible, which will improve our efficiency in helping you solve the issue, rather than making repeated changes here.

    Best Regards,

    YongQing.

    Wednesday, November 13, 2019 2:03 AM