insert scripts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » insert scripts

13 posts, Page 1 of 2

1 2 »»

insert scripts

Rate Topic

Display Mode

Topic Options

Author

Message

mathewspsimon

Posted Sunday, June 14, 2009 5:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 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

ps.

Posted Sunday, June 14, 2009 6:09 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

mathewspsimon

Posted Sunday, June 14, 2009 6:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 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

ps.

Posted Sunday, June 14, 2009 7:33 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

mathewspsimon

Posted Sunday, June 14, 2009 8:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89, Visits: 231

thank you sir.it worked

Post #734599

ps.

Posted Sunday, June 14, 2009 9:09 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

Pradeep Singh

Post #734604

mathewspsimon

Posted Monday, June 15, 2009 3:01 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 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

ps.

Posted Monday, June 15, 2009 3:34 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

mathewspsimon

Posted Monday, June 15, 2009 4:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 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

ps.

Posted Monday, June 15, 2009 4:26 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

mathewspsimon (6/15/2009)

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions