SQL Server Performance Forum – Threads Archive
Can i use bulk import for this case?
Hi guys,<br /><br />I need suggestion for my existing SSIS package which is currently importing csv file. I wanted to use bulk import but in my current situation i cannot implement bulk import. Have a look below and tell me if i can implement bulk import or not?<br /><br />Scenario:<br />Customer upload file (inventory) via WEB interface.<br /><br />1- I need to validate each line that it is in good format (if a row format is invalid update errorcount else update successcount).<br /><br />2- I must not stop procedure even 1 or more lines are not in correct format.<br /><br />3- Please look at the flow and suggest if i can change more to bulk import<br /><br /><font color="blue">PIC 1</font id="blue"><br /><img src=’http://img233.imageshack.us/img233/6703/step1wl9.gif’ border=’0′ /><br /><br /><font color="blue">PIC 2</font id="blue"><br /><img src=’http://img170.imageshack.us/img170/8104/step2jf2.gif’ border=’0′ /><br /><br />What i want is to<br />1- validate each records integrity.<br />2- log success or failure.<br /><br />Any idea [8D].<br /><br />Forgot to mention<br />here is table structure<br />CREATE TABLE [dbo].[tbl](<br />[ID] [int] IDENTITY(1,1) NOT NULL,<br />[col0] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col1] [char](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col2] [int] NULL,<br />[col3] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col5] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col6] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col7] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col8] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />[col9] [datetime] NULL,<br />[col10] [smalldatetime] NULL,<br /> CONSTRAINT [PK_tbl] PRIMARY KEY CLUSTERED <br />(<br />[ID] ASC<br />)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />) ON [PRIMARY]<br /><br />with a trigger<br /><br />TRIGGER [UpdateCI]<br />ON [dbo].[tbl]<br />AFTER INSERT,DELETE<br />AS<br />DECLARE @IsInsert BIT<br />SET @IsInsert=0<br />IF UPDATE (ID)<br />SET @IsInsert=1<br />IF @IsInsert=1<br />BEGIN<br />INSERT INTO tbl1 SELECT ID,col0,col1,col2 FROM INSERTED ins INNER JOIN np ON np.col5=ins.col5 WHERE ins.col0 is NULL<br />INSERT INTO CusInventory SELECT ID,col0,col1,col2 FROM INSERTED WHERE col0 is NOT NULL<br />END<br />ELSE<br />DELETE FROM tbl1 WHERE EXISTS (SELECT TOP 1 ID FROM DELETED WHERE ID=CID)<br /><br /><br /><b><font color="red">and 28k rows take about 8 minutues.</font id="red"></b><br /><br /><br />Waqar.<br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~If you are not sure about the format import data in staging table and modify accordingly Madhivanan Failing to plan is Planning to fail
]]>