InsertRoom

--exec sp_configure 'show advanced options',1
--reconfigure
--exec sp_configure 'Ad Hoc Distributed Queries',1
--reconfigure
----数据抓过来生成临时表
select * into #temp  FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\联讯\文\博文家园.xls";Extended properties="Excel 12.0;HDR=YES;IMEX=1;"')...[sheet1$]
--exec sp_configure 'Ad Hoc Distributed Queries',0
--reconfigure
--exec sp_configure 'show advanced options',0
--reconfigure

 --------插入房间信息
set xact_abort on
begin try
begin tran
declare @villagecode varchar(4),@mark varchar(1)
set @villagecode='003'
set @mark='#'
insert into RoomInfo     
select dbo.getRoomCode(@villagecode,xuhao,roomName)as RoomCode,
ApartmentID=(select ApartmentID from Apartmentinfo where ApartmentCode=SUBSTRING(dbo.getRoomCode(@villagecode,xuhao,roomName),1,8) ),
TypeID,
VillageID=(select VillageId from VillageInfo where VillageCode=@villagecode),
xuhao+@villagecode+RoomName as Name,
[state],
[Acreage],
[PropertyFeeID],
[DeviceFeeID],
null as Memo,
[Acreage] as UserAcreage
from #temp
insert into OwnerInfo       -------插入业主信息
select
ownername,null,null,null,
dbo.getRoomCode(@villagecode,xuhao,roomName)as RoomCode, 1,0,null,null,null,null from #temp

commit tran
print 'Commit'
end try
begin catch  --异常
rollback tran
print 'rollback'
end catch
set xact_abort off  --关闭Try Catch 功能

drop table #temp   ----删除临时表

 

USE [Realty]
GO
/****** Object:  UserDefinedFunction [dbo].[GetRoomCode]    Script Date: 09/03/2009 23:29:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[GetRoomCode](@VillageCode varchar(4),@ApNumber varchar(4),@RoomNumber varchar(4))
returns varchar(12)
as
begin
Declare @outCode varchar(12)
set @ApNumber=Replace(str(convert(int,@ApNumber),4),' ','0')
set @RoomNumber=Replace(str(convert(int,@RoomNumber),4),' ','0')
set @outCode=@VillageCode+@ApNumber+@RoomNumber
return @outCode
end