How to Exec Stored Proc Param from Flat File
in [Programming SQL]
|
Prev: Importing Excel 2007 into XML Data for Shredding?
Next: Word page templates in SSRS, and creating a summary page in SSRS
From: odeddror on 28 Apr 2010 23:20 Hi there, I created a table with two columns FirstName and LastName --MyTable CREATE TABLE [dbo].[myTable]( [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL ) ON [PRIMARY] GO Then I created a Proc with paraters for inserting FirstName and LastName --DynamicParam Create Proc usp_DynamicParam @Fname varchar(50), @Lname varchar(50) AS Insert into myTable(FirstName,LastName) Values (@Fname,@Lname) GO I checked to see if it works Exec usp_DynamicParam 'Ed','Dror' GO Select * from MyTable And it works Now concider this I have a csv file with 10 names And I wanted to create a cursor that execute the StoredProc and read every time from the csv file This is the only method that I can use (I can't use bcp or command shell or ssis) I just want to see if this method is possible to do? Somthing like this DECLARE @List varchar(50) DECLARE c1 CURSOR READ_ONLY FOR Read from My csv file OPEN c1 FETCH NEXT FROM c1 INTO @List WHILE @@FETCH_STATUS = 0 BEGIN Exec usp_DynamicParam + Read from csv file FETCH NEXT FROM c1 INTO @List END CLOSE c1 DEALLOCATE c1 Thanks Oded Dror
From: Uri Dimant on 29 Apr 2010 01:58 oded shalom I am afraid you cannot. Perfect is to use SSIS , but you said you cannot. To open CSV file you need bcp/xp_cmdshell "odeddror" <odeddror(a)cox.net> wrote in message news:A53D4181-7BB0-4BF6-B0EE-2627C4A1D150(a)microsoft.com... > Hi there, > > I created a table with two columns FirstName and LastName > --MyTable > CREATE TABLE [dbo].[myTable]( > [FirstName] [nvarchar](50) NULL, > [LastName] [nvarchar](50) NULL > ) ON [PRIMARY] > GO > > Then I created a Proc with paraters for inserting FirstName and LastName > --DynamicParam > Create Proc usp_DynamicParam > @Fname varchar(50), > @Lname varchar(50) > AS > Insert into myTable(FirstName,LastName) > Values (@Fname,@Lname) > GO > > I checked to see if it works > Exec usp_DynamicParam 'Ed','Dror' > GO > Select * from MyTable > > And it works > > Now concider this > I have a csv file with 10 names > > And I wanted to create a cursor that execute the StoredProc and read every > time from the csv file > > This is the only method that I can use (I can't use bcp or command shell > or ssis) > I just want to see if this method is possible to do? > > > Somthing like this > > DECLARE @List varchar(50) > DECLARE c1 CURSOR READ_ONLY > FOR > Read from My csv file > OPEN c1 > FETCH NEXT FROM c1 > INTO @List > WHILE @@FETCH_STATUS = 0 > BEGIN > > Exec usp_DynamicParam + Read from csv file > > FETCH NEXT FROM c1 INTO @List > END > CLOSE c1 > DEALLOCATE c1 > > Thanks > Oded Dror > > > > >
From: odeddror on 29 Apr 2010 08:21 Uri, Its ok how do I read from a flat file as a stored proc param? What I meant is I want to use this approach and not something else Yes I will xp_cmdshell if necessary Thanks, Oded "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:eE6LYD25KHA.5464(a)TK2MSFTNGP05.phx.gbl... > oded shalom > I am afraid you cannot. Perfect is to use SSIS , but you said you cannot. > To open CSV file you need bcp/xp_cmdshell > > > "odeddror" <odeddror(a)cox.net> wrote in message > news:A53D4181-7BB0-4BF6-B0EE-2627C4A1D150(a)microsoft.com... >> Hi there, >> >> I created a table with two columns FirstName and LastName >> --MyTable >> CREATE TABLE [dbo].[myTable]( >> [FirstName] [nvarchar](50) NULL, >> [LastName] [nvarchar](50) NULL >> ) ON [PRIMARY] >> GO >> >> Then I created a Proc with paraters for inserting FirstName and LastName >> --DynamicParam >> Create Proc usp_DynamicParam >> @Fname varchar(50), >> @Lname varchar(50) >> AS >> Insert into myTable(FirstName,LastName) >> Values (@Fname,@Lname) >> GO >> >> I checked to see if it works >> Exec usp_DynamicParam 'Ed','Dror' >> GO >> Select * from MyTable >> >> And it works >> >> Now concider this >> I have a csv file with 10 names >> >> And I wanted to create a cursor that execute the StoredProc and read >> every time from the csv file >> >> This is the only method that I can use (I can't use bcp or command shell >> or ssis) >> I just want to see if this method is possible to do? >> >> >> Somthing like this >> >> DECLARE @List varchar(50) >> DECLARE c1 CURSOR READ_ONLY >> FOR >> Read from My csv file >> OPEN c1 >> FETCH NEXT FROM c1 >> INTO @List >> WHILE @@FETCH_STATUS = 0 >> BEGIN >> >> Exec usp_DynamicParam + Read from csv file >> >> FETCH NEXT FROM c1 INTO @List >> END >> CLOSE c1 >> DEALLOCATE c1 >> >> Thanks >> Oded Dror >> >> >> >> >> > >
From: Uri Dimant on 29 Apr 2010 08:42 Oded I'd suggest you to have a temporary table to held those parametesrs and then run a cursor to assign the value to variable and pass to the sp CREATE TABLE TmpStList ( stFName varchar (10) NOT NULL, stLName varchar (10) NOT NULL ) go The data file (hawk.dat): "Kelly","Reynold" "John","Smith" "Sara","Parker" The format file (hawk.bcp): 8.0 3 1 SQLCHAR 0 1 "\"" 0 first_quote "" 2 SQLCHAR 0 10 "\",\"" 1 stFName "" 3 SQLCHAR 0 10 "\",\"" 2 stLName "" bulk insert TmpStList from 'C:\hawk.dat' with (formatfile = 'C:\hawk.bcp') select * from TmpStList "odeddror" <odeddror(a)cox.net> wrote in message news:2D4CC00B-7D0E-4949-B698-B2FE0F09AC7F(a)microsoft.com... > Uri, > > Its ok how do I read from a flat file as a stored proc param? > What I meant is I want to use this approach and not something else > Yes I will xp_cmdshell if necessary > > Thanks, > Oded > > "Uri Dimant" <urid(a)iscar.co.il> wrote in message > news:eE6LYD25KHA.5464(a)TK2MSFTNGP05.phx.gbl... >> oded shalom >> I am afraid you cannot. Perfect is to use SSIS , but you said you cannot. >> To open CSV file you need bcp/xp_cmdshell >> >> >> "odeddror" <odeddror(a)cox.net> wrote in message >> news:A53D4181-7BB0-4BF6-B0EE-2627C4A1D150(a)microsoft.com... >>> Hi there, >>> >>> I created a table with two columns FirstName and LastName >>> --MyTable >>> CREATE TABLE [dbo].[myTable]( >>> [FirstName] [nvarchar](50) NULL, >>> [LastName] [nvarchar](50) NULL >>> ) ON [PRIMARY] >>> GO >>> >>> Then I created a Proc with paraters for inserting FirstName and LastName >>> --DynamicParam >>> Create Proc usp_DynamicParam >>> @Fname varchar(50), >>> @Lname varchar(50) >>> AS >>> Insert into myTable(FirstName,LastName) >>> Values (@Fname,@Lname) >>> GO >>> >>> I checked to see if it works >>> Exec usp_DynamicParam 'Ed','Dror' >>> GO >>> Select * from MyTable >>> >>> And it works >>> >>> Now concider this >>> I have a csv file with 10 names >>> >>> And I wanted to create a cursor that execute the StoredProc and read >>> every time from the csv file >>> >>> This is the only method that I can use (I can't use bcp or command shell >>> or ssis) >>> I just want to see if this method is possible to do? >>> >>> >>> Somthing like this >>> >>> DECLARE @List varchar(50) >>> DECLARE c1 CURSOR READ_ONLY >>> FOR >>> Read from My csv file >>> OPEN c1 >>> FETCH NEXT FROM c1 >>> INTO @List >>> WHILE @@FETCH_STATUS = 0 >>> BEGIN >>> >>> Exec usp_DynamicParam + Read from csv file >>> >>> FETCH NEXT FROM c1 INTO @List >>> END >>> CLOSE c1 >>> DEALLOCATE c1 >>> >>> Thanks >>> Oded Dror >>> >>> >>> >>> >>> >> >> >
From: odeddror on 29 Apr 2010 23:55
Uri, Thank you very much I got away with this using ssis with oledb command I thought there is a way to do this with regular TSQL command Oded "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:OQMI3k55KHA.348(a)TK2MSFTNGP02.phx.gbl... > Oded > I'd suggest you to have a temporary table to held those parametesrs and > then run a cursor to assign the value to variable and pass to the sp > > CREATE TABLE TmpStList > ( > stFName varchar (10) NOT NULL, > stLName varchar (10) NOT NULL > ) > go > > The data file (hawk.dat): > > "Kelly","Reynold" > "John","Smith" > "Sara","Parker" > > The format file (hawk.bcp): > > 8.0 > 3 > 1 SQLCHAR 0 1 "\"" 0 first_quote "" > 2 SQLCHAR 0 10 "\",\"" 1 stFName "" > 3 SQLCHAR 0 10 "\",\"" 2 stLName "" > > > > bulk insert TmpStList from 'C:\hawk.dat' > with (formatfile = 'C:\hawk.bcp') > > select * from TmpStList > > > > > "odeddror" <odeddror(a)cox.net> wrote in message > news:2D4CC00B-7D0E-4949-B698-B2FE0F09AC7F(a)microsoft.com... >> Uri, >> >> Its ok how do I read from a flat file as a stored proc param? >> What I meant is I want to use this approach and not something else >> Yes I will xp_cmdshell if necessary >> >> Thanks, >> Oded >> >> "Uri Dimant" <urid(a)iscar.co.il> wrote in message >> news:eE6LYD25KHA.5464(a)TK2MSFTNGP05.phx.gbl... >>> oded shalom >>> I am afraid you cannot. Perfect is to use SSIS , but you said you >>> cannot. To open CSV file you need bcp/xp_cmdshell >>> >>> >>> "odeddror" <odeddror(a)cox.net> wrote in message >>> news:A53D4181-7BB0-4BF6-B0EE-2627C4A1D150(a)microsoft.com... >>>> Hi there, >>>> >>>> I created a table with two columns FirstName and LastName >>>> --MyTable >>>> CREATE TABLE [dbo].[myTable]( >>>> [FirstName] [nvarchar](50) NULL, >>>> [LastName] [nvarchar](50) NULL >>>> ) ON [PRIMARY] >>>> GO >>>> >>>> Then I created a Proc with paraters for inserting FirstName and >>>> LastName >>>> --DynamicParam >>>> Create Proc usp_DynamicParam >>>> @Fname varchar(50), >>>> @Lname varchar(50) >>>> AS >>>> Insert into myTable(FirstName,LastName) >>>> Values (@Fname,@Lname) >>>> GO >>>> >>>> I checked to see if it works >>>> Exec usp_DynamicParam 'Ed','Dror' >>>> GO >>>> Select * from MyTable >>>> >>>> And it works >>>> >>>> Now concider this >>>> I have a csv file with 10 names >>>> >>>> And I wanted to create a cursor that execute the StoredProc and read >>>> every time from the csv file >>>> >>>> This is the only method that I can use (I can't use bcp or command >>>> shell or ssis) >>>> I just want to see if this method is possible to do? >>>> >>>> >>>> Somthing like this >>>> >>>> DECLARE @List varchar(50) >>>> DECLARE c1 CURSOR READ_ONLY >>>> FOR >>>> Read from My csv file >>>> OPEN c1 >>>> FETCH NEXT FROM c1 >>>> INTO @List >>>> WHILE @@FETCH_STATUS = 0 >>>> BEGIN >>>> >>>> Exec usp_DynamicParam + Read from csv file >>>> >>>> FETCH NEXT FROM c1 INTO @List >>>> END >>>> CLOSE c1 >>>> DEALLOCATE c1 >>>> >>>> Thanks >>>> Oded Dror >>>> >>>> >>>> >>>> >>>> >>> >>> >> > > |