February 13, 2014 at 4:34 pm
Please find the script for migration. Here I have attached two supporting documents. One is database diagram and the Excel sheet itself
USE [COONEW]
GO
/****** Object: StoredProcedure [dbo].[GetScheduledData] Script Date: 14/02/2014 10:35:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetScheduledData]
as
DECLARE cur_DataTransfer CURSOR FOR
select * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\Public\Documents\Scheduleddata.xlsx;HDR=YES', 'SELECT * FROM [sheet1$]')
declare @pcode as nvarchar(50)
declare @PRCode as nvarchar(50)
declare @PD as nvarchar(200)
declare @SPNumber as nvarchar(100)
declare @SComment as nvarchar(max)
declare @Intervals as nvarchar(max)
declare @LTService as float
declare @LCost as float
declare @SPRPrice as float
declare @Country as nvarchar(100)
declare @Currency as varchar(10)
declare @Category as nvarchar(500)
declare @VSName as nvarchar(100)
declare @VMName as nvarchar(200)
declare @VName as nvarchar(50)
declare @Date as datetime
declare @REKM as nvarchar(max)
declare @NServices as int
declare @new_identity as int
declare @PID as int
declare @LID as int
declare @IID as int
declare @SMInfoID as int
declare @counter INT = 1
begin
open cur_DataTransfer
fetch next from cur_DataTransfer into @pcode,@PRCode,@PD, @SPNumber, @SComment, @Intervals, @LTService, @LCost, @SPRPrice, @Country, @Currency, @Category, @VSName, @VMName, @VName, @Date, @REKM, @NServices
WHILE @@FETCH_STATUS = 0
begin
INSERT INTO [COONEW].[dbo].ScheduledMaintenanceInfoes
(
CountryId, [CategoryId], [VSegId], [VehicleId], VehicleModel, [DataCollectionDate]
)
SELECT
(SELECT CountryId FROM COONEW.DBO.Countries where CountryName = @Country),(select CategoryId from COONEW.DBO.Categories where CategoryName = @Category),(select VSegId from COONEW.DBO.VehicleSegments where VSegName = @VSName), (select VehicleId from COONEW.DBO.Vehicles where VehicleName =@VName), (select VModelId from COONEW.DBO.VehicleModels where VModelName =@VMName), [DataCollectionDate] = @Date
FROM [COONEW].[dbo].[Categories], [COONEW].[dbo].[Countries], [COONEW].[dbo].[VehicleModels], [COONEW].[dbo].[Vehicles],[COONEW].[dbo].[VehicleSegments], [TestDB].[dbo].[Sheet1$]
set @new_identity = SCOPE_IDENTITY()
declare cur_Parts CURSOR FOR
select PartsId from dbo.Parts where dbo.Parts.CategoryId = @Category AND dbo.Parts.PartsCode = @pcode AND dbo.Parts.PartsRegionalCode = @PRCode AND dbo.Parts.PartsDescription = @PD
open cur_Parts
fetch next from cur_Parts into @PID
WHILE @@FETCH_STATUS = 0
begin
insert into dbo.ScheduledCosts(SMInfoId, PartsId, SPartNumber, SReplaceEveryKM, SNoServicePer100K, SPartRetailPricePerService, SComment, SWeightingFactor)
values(@SMInfoID, @PID, @SPNumber, @REKM, @NServices, @SPRPrice, @SComment, 0)
declare cur_LabourCost CURSOR FOR
select LabourCostId from dbo.LabourCosts where LaborCostPerHour = @LCost
open cur_LabourCost
fetch next from cur_LabourCost into @LID
WHILE @@FETCH_STATUS = 0
begin
declare cur_Interval CURSOR FOR
select IntervalId from dbo.Intervals where Intervals = @Intervals
open cur_Interval
fetch next from cur_Interval into @IID
WHILE @@FETCH_STATUS = 0
begin
insert into dbo.Labours(SMInfoId, IntervalId, LTimePerService, LabourCostId)
values(@SMInfoID, @IID, @LTService, @LID)
IF @counter = 10000
BEGIN
DBCC FREESYSTEMCACHE -- Clear System Cache
DBCC FREEPROCCACHE -- Clear Proc Cache
SET @counter = 0 -- Restarting counter
END
fetch next from cur_Interval into @IID
fetch next from cur_LabourCost into @LID
fetch next from cur_Parts into @PID
fetch next from cur_DataTransfer into @pcode,@PRCode,@PD, @SPNumber, @SComment, @Intervals, @LTService, @LCost, @SPRPrice, @Country, @Currency, @Category, @VSName, @VMName, @VName, @Date, @REKM, @NServices
SET @counter = @counter + 1
END
END
END
END
END
CLOSE cur_Interval
CLOSE cur_LabourCost
CLOSE cur_Parts
CLOSE cur_Data_Transfer -- Closing cursor
DEALLOCATE cur_Interval
DEALLOCATE cur_LabourCost
DEALLOCATE cur_Parts
DEALLOCATE cur_Data_Transfer -- De-allocatin**
Thank you in advance
February 13, 2014 at 4:50 pm
What's the question or the point of this post?
February 13, 2014 at 8:05 pm
Hi
I am new to SQL. I just want to migrate data from Excel sheet to SQL. I cannot directly insert data through Tasks>>Import Data, because my main table '[dbo].[ScheduledMaintenanceInfoes]'(given below) holds only primary key and foreign keys. I am getting error in script like "cursor_name doesnot exist"
ScheduledMaintenanceInfoes Table
CountryIdintUnchecked
SMInfoIdintUnchecked
CurrencyIdintUnchecked
CategoryIdintUnchecked
VSegIdintUnchecked
VehicleIdintUnchecked
VehicleModelintUnchecked
DataCollectionDatedatetimeUnchecked
EIdintUnchecked
PIdintUnchecked
FIdintUnchecked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply