Using a SQL Stored Procedure to import an Excel spreadsheet

Question

Using a SQL Stored Procedure to import an Excel spreadsheet

karschnike

SSC Veteran

Points: 293
More actions
November 18, 2008 at 11:28 am

#215521

I am not a programmer by any stretch, so although this is a very simple question for most (if not all) on this site, it is beyond me. The programmer in my company has written a stored procedure to put data into two different tables in one of our databases. I have been tasked with using this stored procedure to import data from an Excel spreadsheet containing seven columns and 513 rows. I have no idea how to structure the SQL syntax to execute the stored procedure against all 513 rows.
Can anyone help? If so, please try to put responses in very simple layman's terms. I am way out of my league here.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This information you have provided is rather skimpy to say the least. If it is possible can you read the article in my signature block and provide as much of the information that it recommends? With the additional information I am sure many will be more than willing to assist you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

karschnike (11/18/2008)
I have been tasked with using this stored procedure to import data from an Excel

It would be really helpful if you posted the stored procedure so we can take a look-see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krayknot SSCertifiable Points: 7998 More actions · Answer 3

karschnike (11/18/2008)
I am not a programmer by any stretch, so although this is a very simple question for most (if not all) on this site, it is beyond me. The programmer in my company has written a stored procedure to put data into two different tables in one of our databases. I have been tasked with using this stored procedure to import data from an Excel spreadsheet containing seven columns and 513 rows. I have no idea how to structure the SQL syntax to execute the stored procedure against all 513 rows.
Can anyone help? If so, please try to put responses in very simple layman's terms. I am way out of my league here.
Thanks

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

OR, check this out

http://support.microsoft.com/kb/321686

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

karschnike SSC Veteran Points: 293 More actions · Answer 4

The stored procedure is below. The spreadsheet contains the following information related to the sproc: OilLabID, LabSamplePointID, CustomerSiteID, SampleLocationDesc, EquipmentDescription and Capacity.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER PROCEDURE SaveSamplePointSchedule

(

@SamplePointScheduleIDint = NULL,

@OilLabID int,

@LabSamplePointIDvarchar(50),

@IntervalInDaysint = NULL,

@NextCollectionDatedatetime = NULL,

@NonCalendarCollectionCycleIntervalint = NULL,

@NonCalendarCollectionCycleTypeIDint= NULL,

@CustomerSiteID int,

@SamplePointLocationDescvarchar(50),

@EquipmentDescriptionvarchar(50),

@Criticalityint= NULL,

@Capacitynvarchar(50)= NULL,

@IgnoreSamplePointInfo bit = 0-- If we just want to update the schedule, set to 1

)

AS

SET NOCOUNT ON

-- Make sure to null out invalid cycle intervals

IF (0 = ISNULL(@NonCalendarCollectionCycleTypeID, 0))

SET @NonCalendarCollectionCycleTypeID = NULL

IF (0 = @NonCalendarCollectionCycleInterval AND 0 = ISNULL(@NonCalendarCollectionCycleTypeID, 0))

SET @NonCalendarCollectionCycleInterval = NULL

-- Make sure the integer values are valid

IF (0 = @SamplePointScheduleID)

SET @SamplePointScheduleID = NULL

IF (0 = @IntervalInDays)

SET @IntervalInDays = NULL

IF (0 = @NonCalendarCollectionCycleInterval)

SET @NonCalendarCollectionCycleInterval = NULL

IF (0 = @Criticality)

SET @Criticality = NULL

IF (0 = @Capacity)

SET @Capacity = NULL

IF (@SamplePointScheduleID IS NULL)

BEGIN

INSERT SamplePoint(CustomerSiteID, SamplePointLocationDesc, EquipmentDescription, Criticality, Capacity, IsActive)

VALUES(@CustomerSiteID, @SamplePointLocationDesc, @EquipmentDescription, @Criticality, @Capacity, 1)

DECLARE @SamplePointID int

SET @SamplePointID = SCOPE_IDENTITY()

INSERT SamplePointSchedule(SamplePointID, OilLabID, LabSamplePointID, IntervalInDays, NextCollectionDate, NonCalendarCollectionCycleInterval, NonCalendarCollectionCycleTypeID, IsActive)

VALUES(@SamplePointID, @OilLabID, @LabSamplePointID, @IntervalInDays, @NextCollectionDate, @NonCalendarCollectionCycleInterval, @NonCalendarCollectionCycleTypeID, 1)

END

ELSE

BEGIN

IF (0 = @IgnoreSamplePointInfo)

BEGIN

UPDATE SamplePoint

SETCustomerSiteID = @CustomerSiteID,

SamplePointLocationDesc = @SamplePointLocationDesc,

EquipmentDescription = @EquipmentDescription,

Criticality = @Criticality,

Capacity = @Capacity

WHERESamplePointID IN (SELECT SamplePointID FROM SamplePointSchedule WHERE SamplePointScheduleID = @SamplePointScheduleID)

END

UPDATE SamplePointSchedule

SET

OilLabID = @OilLabID,

LabSamplePointID = @LabSamplePointID,

IntervalInDays = @IntervalInDays,

NextCollectionDate = @NextCollectionDate,

NonCalendarCollectionCycleInterval = @NonCalendarCollectionCycleInterval,

NonCalendarCollectionCycleTypeID = @NonCalendarCollectionCycleTypeID

WHERESamplePointScheduleID = @SamplePointScheduleID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Sounds like a one time task... recommend you use SSIS to import the data into a "staging table" and then do an insert into the final table(s) from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)