Author |
Topic |
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-06-04 : 12:40:48
|
I have a table called _phy_greenville. Its a table that was imported from an excel file. I need to take the values in this table, and pass them to the following stored procedure. This stored proc create the physician record correctly for me when doing one record at a time. What I need to do is pass ALL record in this table (_phy_greenville) into this stored proc. Is there anyway I can loop through, or do some sort of bulk insert?I tried the following, but obviously this does not workBEGIN TRANSACTIONEXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_GreenvilleSELECT * FROM Person WHERE PersonOrganizationID = 31ROLLBACK TRANSACTION Here is the whole stored procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[pInsertPersonEx] ( @Active bit, @PersonPrefix varchar(5), @PersonFirstName varchar(50), @PersonLastName varchar(50), @PersonSuffix varchar(20), @PersonEmail varchar(100), @PersonPassword varchar(20), @PersonOrganizationID int, @PersonDepartment varchar(50), @PersonTitle varchar(100), @PersonPhone varchar(10), @PersonFax varchar(10), @PersonInfoRequested bit, @PersonHCTrained bit = NULL, @PersonHCTrainedDate DateTime = NULL, @AddressAddress1 varchar(100), @AddressAddress2 varchar(100), @AddressCity varchar(100), @StateId int, @AddressPostalCode varchar(10),-- @DepartmentID int, @RoleID int )asIF @PersonHCTrainedDate = '01/05/1900' SET @PersonHCTrainedDate = NULLset nocount ondeclare @PersonId int, @AddressTypeID intinsert Person( Active, PersonPrefix, PersonFirstName, PersonLastName, PersonSuffix, PersonEmail, PersonPassword, PersonOrganizationID, PersonDepartment, PersonRegistrationDate, PersonLicenseAgreement, PersonTitle, PersonPhone, PersonFax, PersonInfoRequested, PersonHCTrained, PersonHCTrainedDate)values( @Active, @PersonPrefix, @PersonFirstName, @PersonLastName, @PersonSuffix, @PersonEmail, convert(varbinary,@PersonPassword), @PersonOrganizationID, @PersonDepartment, getdate(), 0, /* TODO need to get this from form */ @PersonTitle, @PersonPhone, @PersonFax, @PersonInfoRequested, @PersonHCTrained, @PersonHCTrainedDate)set @PersonID = IDENT_CURRENT('Person')/* look up the default address type */select @AddressTypeID = AddressTypeIDfrom AddressTypewhere AddressTypeDisplayName = 'Work'exec pInsertPersonAddress @AddressAddress1, @AddressAddress2, @AddressCity, @AddressPostalCode, @StateID, @PersonID, @AddressTypeID, 1 /* this proc always inserts the default address *//* - Schema change. Department now a varcharexec pInsertPersonDepartment @PersonID, @DepartmentID, 1 /* this proc always inserts the default department */*/ exec pInsertPersonRole @PersonID, @RoleIDINSERT INTO tblHospitalsCoordinated( intPersonID ,intHospitalID ,dtmCreatedDate ,dtmModifiedDate ,strModifiedBy)VALUES(@PersonID,@PersonOrganizationID,GetDate(),GetDate(),'') select @PersonID |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 13:19:14
|
[code]DECLARE @PKCol intSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHILE @PKCol IS NOT NULLBEGINyour query hereSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHERE PKCol > @PKColEND[/code]I still cant understand why you are ROLLBACKing transaction in your query though |
|
|
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-06-04 : 13:40:12
|
I am just Rolling it back because I was testing it. No other reason. Let me try that. But wait, what exactly would I put for the insertDECLARE @PKCol intSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHILE @PKCol IS NOT NULLBEGIN'How do i pass the right Params to the stored proc?EXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_Greenville WHERE ID = @PKColSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHERE PKCol > @PKColEND |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 13:53:00
|
You cant do EXEC...SELECT. you need to get values in variables and pass it to spDECLARE @PKCol intSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHILE @PKCol IS NOT NULLBEGINSELECT @FirstName=FirstName,@LastName=LastName,@Suffix=Suffix,@Email=Email,@FullName=CAST((LEFT(FirstName, 1) + LastName)as varbinary),@PrimarySpecialty=PrimarySpecialty, @OffPhone=REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''),@Street=Street,@City=City,@Zip=Zip FROM _Phy_Greenville WHERE ID = @PKCol'How do i pass the right Params to the stored proc?EXEC pInsertPersonEX SELECT 1, 'Dr.', @FirstName, @LastName, @Suffix, @Email, @FullName, 31, @PrimarySpecialty, 'Student', @OffPhone, NULL, 0, 0, NULL, @Street, NULL, @City, 41, @Zip,3 FROM _Phy_Greenville WHERE ID = @PKColSELECT @PKCol=MIN(PKCol)FROM _phy_greenvilleWHERE PKCol > @PKColEND Also remember to declare the variables used before while loop |
|
|
Harry C
Posting Yak Master
148 Posts |
Posted - 2008-06-04 : 15:02:47
|
Got it, thanks!HC |
|
|
|
|
|