insert scripts

  • I have a table as follows:

    CREATE TABLE [dbo].[ReorderS]([Item_Code] [nvarchar](50) NULL,

    [Quantity] [int] NULL ) ON [PRIMARY]

    -- Tables values are

    insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN00001',20)

    insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN00002',15)

    insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN0011',50)

    WHEN I RUN THE BELOW SCRIPT

    DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50),@val3 varchar(50),@Sql varchar(8000)

    SELECT @val1 ='SQLSRV',@val2=[Item_Code],@val3=[Quantity],@val4=CONVERT(VARCHAR(10), GETDATE(), 101)

    FROM [ReorderS]

    SET @sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'

    print @sql

    --- I get the output as

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN0011',110); End;

    THE OUT PUT I WANT IS AS FOLLOWS --

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN0011',50); End;

    0

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN00002',15); End;

    1

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN00001',20); End;

    DEAR GURUS PLEASE SOME HELP WITH THIS.

  • I'm not sure if this is the most efficient code but perhaps does your work

    select 'Begin XXOH_INV_INTERFACE_PKG.insert_trx(' +''''+ convert(varchar,getdate(),101) + ''''+

    ',' +''''+'SQLSRV' + '''' +',' + '''' + item_code +'''' +','+ cast(quantity as varchar) + ');END;'

    from [dbo].[ReorderS]

    OUTPUT

    -------------------

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN00001',20);END;

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN00002',15);END;

    Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN0011',50);END;



    Pradeep Singh

  • Hi Sir,

    That works as far as the output is concerned but I need to use dynamic sql since i am using this to execute a statement to update in the Oracle database.

  • using cursors might not be the best option but still using cursors in this example.

    let us know if it's fine.

    DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50),@val3 varchar(50),@Sql varchar(8000);

    set @val1='SQLSRV'

    set @val4=CONVERT(VARCHAR(10), GETDATE(), 101)

    Declare sample_cur cursor for

    SELECT Item_Code,Quantity

    FROM ReorderS

    OPEN sample_cur

    Fetch next from sample_cur into @val2, @val3

    While (@@fetch_status-1)

    BEGIN

    SET @sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'

    print @sql

    Fetch next from sample_cur into @val2, @val3

    END

    Close sample_cur

    Deallocate sample_cur



    Pradeep Singh

  • thank you sir.it worked

  • 🙂



    Pradeep Singh

  • hello sir,there seems to be one issue with this.the quantity column in the table ReorderS is a number.Using this way I find that value is in some case getting rounded off like 100.29 becomes 100.3 and 121.08 becomes 121.10>Is there some way to avoid this.

  • The table script u gave in ur first post defines Quantity as Integer.

    I modified the column by running

    alter table reorderS alter column Quantity decimal(10,2), you can increase the precision (here 2) or use float data type as well...

    I also updated the quantity column

    update reorders set quantity=quantity*6.123

    and the same cursor displays records perfectly.



    Pradeep Singh

  • sir,that was a dummy table for this cae.I dont know what is the reason,is it becuase there are 1000 rows?the output of the cursor shows as I mentioned as 121.1 instead of 121.08.And this is not happening for all only an few.Is there a way to prevent this?

  • mathewspsimon (6/15/2009)


    sir,that was a dummy table for this cae.I dont know what is the reason,is it becuase there are 1000 rows?the output of the cursor shows as I mentioned as 121.1 instead of 121.08.And this is not happening for all only an few.Is there a way to prevent this?

    I just wrote that in my previous post. did u try that?

    -----------------------Try this if ur base table can accept decimal numbers in quantity col---

    DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50), @val3 decimal(10,2), @Sql varchar(8000);

    set @val1='SQLSRV'

    set @val4=CONVERT(VARCHAR(10), GETDATE(), 101)

    Declare sample_cur cursor for

    SELECT Item_Code,Quantity

    FROM ReorderS

    OPEN sample_cur

    Fetch next from sample_cur into @val2, @val3

    While (@@fetch_status-1)

    BEGIN

    SET @sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+cast(@val3 as varchar)+'); End;'

    print @sql

    Fetch next from sample_cur into @val2, @val3

    END

    Close sample_cur

    Deallocate sample_cur



    Pradeep Singh

  • sir,

    that didnt work.what I did was like this

    ''' + str(@val2 ,21, 2)+ ''' and it worked

  • am glad u got it work!!

    but still few things not clear...

    @val2 is used to store Item_code in the cursor but u faced problem with quantity column??



    Pradeep Singh

  • sorry that was a typo mistake.thanks any for the help

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply