Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

insert scripts Expand / Collapse
Author
Message
Posted Sunday, June 14, 2009 5:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
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.
Post #734581
Posted Sunday, June 14, 2009 6:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,242, Visits: 3,647
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
Post #734583
Posted Sunday, June 14, 2009 6:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
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.
Post #734584
Posted Sunday, June 14, 2009 7:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,242, Visits: 3,647
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
Post #734592
Posted Sunday, June 14, 2009 8:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
thank you sir.it worked
Post #734599
Posted Sunday, June 14, 2009 9:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,242, Visits: 3,647





Pradeep Singh
Post #734604
Posted Monday, June 15, 2009 3:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
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.

Post #734843
Posted Monday, June 15, 2009 3:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,242, Visits: 3,647
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
Post #734852
Posted Monday, June 15, 2009 4:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
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?
Post #734880
Posted Monday, June 15, 2009 4:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,242, Visits: 3,647
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
Post #734881
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse