Help Optimising SP

HI All,
I am busy importing records from excel into a SQL table.
I am using:
insert into PLImport2 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\work\coke\Detailed P&L June RE.xls', 'SELECT * FROM
[Import1]')
The excle sheet has 49000 records in it, which normally takes about 20-22
seconds, which includes opening the excel file of course.
Which normally
I have an instead of trigger, so that I can replace certain column on input.
With the trigger running it took 1:43:20
Could someone see if this could be optimised. Or is there a better way of
doing things.
Thanks
RObert
PS Below is the table and sp. Please dont worry too much about the field
name, ie [1], as this is how it was created when come over from excel.
This acts as a staging table , and will be put into the proper table once
all the validation is taken care of.
Thanks
CREATE TABLE [dbo].[PLImport2] (
[RowID] float NULL,
[PF] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PFDesc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Product] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostingPeriod] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1] float NULL,
[2] float NULL,
[3] float NULL,
[4] float NULL,
[5] float NULL,
[6] float NULL,
[7] float NULL,
[8] float NULL,
[9] float NULL,
[10] float NULL,
[11] float NULL,
[12] float NULL,
[111] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[21] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[31] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[41] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[51] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[61] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[71] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[81] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[91] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[101] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[112] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[121] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[122] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[22] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[32] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[42] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[52] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[62] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[72] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[82] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[92] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[102] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[113] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[123] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[13] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[23] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[33] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[43] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[53] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[63] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[73] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[83] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[93] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[103] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[114] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[124] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[PLImport2_tri] ON [dbo].[PLImport2]
WITH EXECUTE AS 'dbo'
INSTEAD OF INSERT
AS
BEGIN
--Declare some variables that we will use in the cursor
Declare @.profitcenter varchar(50), @.Acc varchar(75), @.pf varchar(50),
@.oldAcc varchar(75), @.RowID int, @.cnt int
set @.cnt = 1
--Declare the cursor and fetch records from inerted table
DECLARE OneChange CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC
FOR SELECT RowID, PF, Acc FROM inserted
OPEN OneChange
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
--loop thour the curso performing task on each record
WHILE (@.@.FETCH_STATUS=0)
BEGIN
-- If there are no records inserted already, insert at least one
if (select count(*) from plimport2) = 0
begin
insert into plimport2 select top 1 * from inserted
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
end
--now select the last inserted record, by getting the Max of field F!
select @.pf = PF, @.oldAcc = Acc from plimport2 where RowID = (select
max(RowID) from plimport2)
--compare the results if null, replace with old value
if @.pf is null
begin
print 'PF is null'
end
else
begin
insert into plimport2 select * from inserted i where i.RowID=@.RowID
if @.profitcenter is null
set @.profitcenter = @.pf
else
begin
set @.pf = @.profitcenter
end
update plimport2 set PF=@.profitcenter where PF is null
if @.Acc is null
set @.Acc = @.oldAcc
else
begin
set @.oldAcc = @.Acc
end
update plimport2 set Acc=@.Acc where Acc is null
print 'Pf - '+@.pf+' Profic Center = '+@.profitcenter+' Dep = '+@.Acc+'
Status = '+str(@.@.FETCH_STATUS)
end
set @.cnt = @.cnt+1
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
print 'next Status = '+str(@.@.FETCH_STATUS)
end
ENDRobert,
I would consider another alternative:
1. create a staging table and populate it from Excel:
select * into staging from OPENROWSET
2. issue one set-based UPDATE followed by one set-based insert. For
instance, try this:
[url]http://sql-server-tips.blogspot.com/2006/06/mimicking-merge-statement-in-sql.html[
/url]|||Hi There,
You should give sample data ,DDL and required result to work on.
I think you may try this as Alexander suggested (I am giving SQL 2000
version)
Update those rows taht exists
Update tableX set amt=amt+(Select a from tableY where
tablex.PKID=tabley.PKID) ,col2=xy,
col3=xz
where exists(Select a from tableY where tablex.PKID=tabley.PKID)
insert those rows that doesnot exists
insert into tableX (col1,col2,col3) Select * from tabley where Not
exists(Select a from tableY where tablex.PKID=tabley.PKID)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Alexander Kuznetsov wrote:
> Robert,
> I would consider another alternative:
> 1. create a staging table and populate it from Excel:
> select * into staging from OPENROWSET
> 2. issue one set-based UPDATE followed by one set-based insert. For
> instance, try this:
> http://sql-server-tips.blogspot.com...ent-in-sql.html[
/color]

Help needed with this Error message while running a DTS package

Hi

I am trying to import a excel file into a table but when i run it i am getting this error and i am not sure what this eror is

- Copying to [ICCStatements].[dbo].[Sheet1$] (Error)

Messages

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column "AdminShowInKit" (148) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (102)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (102)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Sheet1$" (89) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Any help will be appreciated.. RegardsKArenThe key error is this:

Error 0xc020901c: Data Flow Task: There was an error with input column "AdminShowInKit" (148) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)

There seems to be an error in the AdminShowInKit column. Possibly a missing value.

help me to solve this

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.