importing using stored procedure

[Machiaveli]

Hello,

I have some access mdb-files which i regurlarly import in sql server. The thing is i want to automate some steps during this proces.

The mdb-files are from different companies but the datamodel remains the same. Before i import them, i'll append them to a temporary table with an extra column for the companyname to be filled in from a form in access. This is because of the autonummers in each mdb, so with the companyname it will be unique key.

After that i'll append them to a normalized table so i have alle companies in one table.

Now i want to automate these steps by using stored procedure. I know that i can import them with dts, but i doesn't allow me to use the tempdb.

Is it possible to do it in a SP?

 

[collierd]

Have you tried bulk insert
Just a suggestion I have never used it
I would normally use DTS

Damian.

 

[Glowworm27]

Yes you can import using a stored proc, but you need to have a bulk insert definition file that tells the bulk insert about your data.

here is a stored proc I use to import invoice data
and the bulk insert def. file.

But i agree with collierd, I would use dts to import data.

code for SP

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

ALTER  procedure dbo.up_Myfileimport
	@simportfilename nvarchar(50),
	@simportfilepath nvarchar(100),
	@dimportInvdate datetime,
	@dimportfiledate datetime,
	@sformatfilepath nvarchar(100),
	@dimportdate datetime,
	@nisheader integer
as

-- imports files into the mirror tables

declare @ssql nvarchar(1000), 
	@stargettable nvarchar(50), 
	@nerrorno int, 
	@nErrLayer int,
	@scomment varchar(300),
	@nimportid int,
	@nBulkErrorNo int,
	@sstoreno char(4),
	@dinvdate datetime

set @nerrorno = 0
Set @nErrLayer = 0
set @scomment = ""
set @sstoreno = substring(@simportfilename, Len(@simportfilename)-7, 4)


-- has the import file already been imported?
--New
if exists(select *
	from importlog
	where filename = @simportfilename
		and Invdate =@dimportInvdate
		and resultcode <> -2)

begin
	-- get the id and comment
	select @nimportid = id, 
		@scomment = comment
	from importlog
	where filename = @simportfilename
	and Invdate =@dimportInvdate

	-- update the record
	update importlog
	set comment = @scomment+ ". Attempted re-import on "+
			cast(@dimportdate as varchar(20))
	from importlog
	where id = @nimportid

	--print "Attempted re-import of import item "+
	--		cast(@nimportid as varchar(20))

	--ok well it was imported once, but see if something failed???? see if any records exists for that
	-- store for that day, if any exist just return normaly with a -1 otherwise, if no records 
	-- exists re-import the data

	if exists(Select * from invoices where storeno = @sstoreno and trandate = @dimportInvdate)
	Begin
		--Data already exists so return -1 do not import the record
		return 9999
	End
	Else
	Begin
		--No data for this store was found, so try to re-import the data
		update importlog
		set comment = @scomment+ ". Attempting to re-import on "+
			cast(@dimportdate as varchar(20))
		,resultcode = -2, distresult=-1
		from importlog
		where id = @nimportid
		select @nerrorno = @@error, @scomment = "Attempting import"

		
	End
end
else
begin
	exec up_setresults @simportfilename,  @dimportfiledate, null,
		@dimportdate, null, -2, "Attempting import..."
	select @nerrorno = @@error, @scomment = "Attempting import"
	set @nimportid = @@identity
	
end 

	
set nocount on
begin tran
	
if @nerrorno = 0
begin	-- begin layer 1 Truncating tables and bulk insert
	Set @nerrlayer = 1	
	if @nisheader = 1
	begin 
		truncate table tmpmirrorhdr
		set @stargettable = "mirrorhdr"
		set @sformatfilepath = @sformatfilepath + "hdrformat.sql"
	end
	else
	begin
		truncate table tmpmirrordtl	
		set @stargettable = "mirrordtl"
		set @sformatfilepath = @sformatfilepath + "dtlformat.sql"

	end

	-- the bulk insert
	set @ssql = "Bulk insert tmp"+ @stargettable +
		" from '" + @simportfilepath + @simportfilename + "' " + 
		" with (formatfile = '" + @sformatfilepath + "')"
	--print @ssql
	
	exec sp_executesql @ssql
	-- layered error tracking to be safe...
	select @nerrorno = @@error, @scomment = "Bulk insert command"	

end

if @nerrorno = 0
begin
	-- -- begin layer 2 move the data to the appropriate table
	Set @nerrlayer = 2	
	if @nisheader = 1
	begin	
		-- get the internal date
		select @dinvdate = convert(datetime,trandate) from tmpmirrorhdr
		-- correct the store number
		update tmpmirrorhdr
			set tostoreno = storeno
			where storeno<>@sstoreno
		update tmpmirrorhdr	
			set importid= @nimportid,
			storeno = @sstoreno
		select @nerrorno = @@error, @scomment = "Update mirror"	
		if @nerrorno=0
		begin
			insert mirrorhdr 
			select * from tmpmirrorhdr  where Type <= '6'	
			select @nerrorno = @@error, @scomment = "Insert tmpMirrorHDR"		
			-- get the rejects
			if @nerrorno<>0
				insert rejecthdr
					select * from tmpmirrorhdr	
		end 	
	end
	else
	begin
		select @dinvdate = convert(datetime,trandate) from tmpmirrordtl
		update tmpmirrordtl
			set todiv = div,
			todept = dept
			where div+dept<>@sstoreno
		update tmpmirrordtl
			set importid = @nimportid,
			div = substring(@sstoreno, 1, 2),
			dept = substring(@sstoreno, 3, 2)
		-- correct the store number

		if @nerrorno=0
		begin
			insert mirrordtl 
				select * from tmpmirrordtl where (Type <= '6' )
			select @nerrorno = @@error, @scomment = "Insert tmpMirrorDTL"	
			-- get the rejects
			if @nerrorno<>0
				insert rejectdtl
					select * from tmpmirrordtl
		end 	
	end
	-- check for dupes
	if @nerrorno <> 0
		insert errorlog(errorno, comments)
			select @nerrorno, "Import error"
	if @nerrorno = 2627
	begin
		if @nisheader = 1
			insert errorlog (errorno, comments)
			select 2627, "Duplicate: "+@simportfilename+
				", Invoice: " + t1.transno
				from tmpmirrorhdr t1
				inner join mirrorhdr h1
				on t1.storeno = h1.storeno
				and t1.transno = h1.transno
		else
			insert errorlog (errorno, comments)
			select 2627, "Duplicate: "+@simportfilename+
				", Invoice: " + t1.transno+
				", Line: "+t1.invlineno
				from tmpmirrordtl t1
				inner join mirrordtl d1
				on t1.div = d1.div
				and t1.dept = d1.dept
				and t1.transno = d1.transno
				and t1.invlineno = d1.invlineno
	end
end

if @nerrorno = 0
begin	-- begin layer 3 Completing Results 
	Set @nerrlayer = 3	
	
	set @scomment="Completed on "+cast(getdate() as varchar(30))+"."
	
	exec up_setresults @simportfilename,  @dimportfiledate, @dimportInvdate, 
		@dimportdate, 
		@dimportdate, 1, @scomment


	set @nerrorno = @@error
	if @nerrorno <> 0
	begin 
		rollback tran
		exec up_errorlog @dlogdate = getdate,
				@nerrorno = @nerrorno,
				@nerrorlevel =@nerrlayer,
				@sComments = @scomment
		return 333
	end
end
else
begin
	set @scomment = "Error number " + cast(@nerrorno as char(10))
	exec up_setresults @simportfilename, @dimportfiledate, @dimportInvdate, 
		@dimportdate, 
		null, @nerrorno, @scomment
	set @nerrorno = @@error
	if @nerrorno <> 0
	begin 
		rollback tran
		exec up_errorlog @dlogdate = getdate,
				@nerrorno = @nerrorno,
				@nerrorlevel =@nerrlayer,
				@sComments = @scomment
		return 666
	end
end

commit tran
return 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

file for bulk insert "hdrformat.sql"

7.0
39
1     SQLCHAR  0    2   ""   1    RecId
2     SQLCHAR  0    1   ""   2    Status
3     SQLCHAR  0    1   ""   3    Type
4     SQLCHAR  0    6   ""   4    TransNo
5     SQLCHAR  0    8   ""   5    MemoNo
6     SQLCHAR  0    8   ""   6    TranDate
7     SQLCHAR  0    4   ""   7    StoreNo
8     SQLCHAR  0    4   ""   8    ToStoreNo
9     SQLCHAR  0    6   ""   9    AcctNo
10    SQLCHAR  0    6   ""   10   ClerkNo
11    SQLCHAR  0    1   ""   11   Terms
12    SQLCHAR  0    1   ""   12   IndOrBus
13    SQLCHAR  0    30  ""   13   Name
14    SQLCHAR  0    25  ""   14   Add1
15    SQLCHAR  0    25  ""   15   Add2
16    SQLCHAR  0    15  ""   16   City
17    SQLCHAR  0    2   ""   17   State
18    SQLCHAR  0    10  ""   18   Zip
19    SQLCHAR  0    10  ""   19   Phone
20    SQLCHAR  0    10  ""   20   BusPhone
21    SQLCHAR  0    4   ""   21   BusPhoneExt
22    SQLCHAR  0    8   ""   22   Make
23    SQLCHAR  0    15  ""   23   Model
24    SQLCHAR  0    2   ""   24   ModelYear
25    SQLCHAR  0    10  ""   25   UnitNo
26    SQLCHAR  0    6   ""   26   Mileage
27    SQLCHAR  0    10  ""   27   TagNo
28    SQLCHAR  0    3   ""   28   NatAcctMfg
29    SQLCHAR  0    10  ""   29   NatAcctNo
30    SQLCHAR  0    10  ""   30   PONo
31    SQLCHAR  0    3   ""   31   ContractPricingMfg
32    SQLCHAR  0    10  ""   32   ContractPriceClaimNo
33    SQLCHAR  0    2   ""   33   ARSalesNo
34    SQLCHAR  0    1   ""   34   CrOverRideCode
35    SQLCHAR  0    6   ""   35   CrOverRidePassword
36    SQLCHAR  0    10  ""   36   SuppInvNo
37    SQLCHAR  0    8   ""   37   SuppInvDate
38    SQLCHAR  0    8   ""   38   SuppRecdDate
39    SQLCHAR  0    1   "\r\n"   39   EOR

hope this helps


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Machiaveli]

Hi,

Thnx for the example, but i think it's a little bit to complex for me to use it. I was also thinking of using the local package of dts in a procedure.

Is that also possible? I need something like this for the form in access:

onclick: executing the local package on sql server
then some stored procedures will run to do the data manipulation.