两张结构不同的表，从一张表导入数据到另外一张表

<wbr></wbr>

//插入应用
INSERT INTO EquipmentApply
(ApplyPlat, ApplyID1, ApplyPerson, ApplyManager, ApplyUseto,ApplyInOutTypeID,
ApplyRemark, ApplyDate, ApplyInputPerson)
SELECT 10 AS ApplyPlat, ApplyMyID AS ApplyID1, ApplyPerson ASApplyPerson,
ApplyPerson AS ApplyManager, 2 AS ApplyUseto, 2 ASApplyInOutTypeID,
ApplyReson AS ApplyRemark, ApplyDate, '33' ASApplyInputPerson
FROM Equipment_MyOfficeApply
WHERE (ApplyMyID = 2003)

使用存储过程的例子：

第一步>创建存储过程

--创建存储过程时,最好现在查询分析器中进行调试,调试好以后再创建存储过程

<wbr></wbr>

CREATE PROCEDURE [dbo].[ApplyOK] @MyapplyID int ,@InputPersonID int ,@RetIno Varchar(100) output AS

declare @NewApplyID int

declare @cgCount int

declare @appstate int

--检查状态

select @appstate =( SELECT ApplyState FROMEquipment_MyOfficeApply WHERE (ApplyMyID = @MyapplyID))

if @appstate<>8begin

if @appstate=-1 begin

select @RetIno= '该申请未曾通过审批，不符合办结条件'

return

end

else if @appstate=9 begin

select @RetIno= '该申请已经办结'

return

end

--- 其他情况

select @RetIno= '该申请不符合办结条件'

return

end

<wbr></wbr>

--检查是否有领取的物品量超过库存的情况

select @cgCount =(SELECT COUNT(*) AS cgCount FROMV_MyOfficeApplyDetail WHERE (MyApplyID = @myapplyid) AND (Remain< 0))

if @cgCount>0 begin--如果领取的物品量超过了库存量则不允许提交

select @RetIno= '领取的物品量超过了库存量则不允许办结申请'

end

else begin

--可以正常进行领取物品处理

--1>补充申请

begin transaction --开始事务

-- 执行登记操作

-- A> //插入登记申请记录

INSERT INTO EquipmentApply

(ApplyPlat, ApplyID1, ApplyPerson, ApplyManager,ApplyUseto, ApplyInOutTypeID,

ApplyRemark, ApplyDate, ApplyInputPerson)

SELECT 10 AS ApplyPlat, ApplyMyID AS ApplyID1,ApplyPerson AS ApplyPerson,

ApplyPerson AS ApplyManager, 2 AS ApplyUseto, 2 ASApplyInOutTypeID,

ApplyReson AS ApplyRemark, ApplyDate, '33' ASApplyInputPerson

FROM Equipment_MyOfficeApply

WHERE (ApplyMyID = @myapplyid)

--获得新申请的ID

SELECT @NewApplyID = @@IDENTITY

-- B> //插入申请明细记录

INSERT INTO EquitmentInOutReg

(ApplyID, EquipmentID, Number)

SELECT @NewApplyID AS ApplyID, EquipmentID,ApplyNumber AS Number

FROM EquipmentOffice_MyEquiplist

WHERE (MyApplyID = @myapplyid)

-- C> //数据进行领取物品后的数据计算

UPDATE EqupmentMain

SET Quantity = Quantity - t2.ApplyNumber

FROM EqupmentMain t1 , EquipmentOffice_MyEquiplistt2

WHERE (t2.MyApplyID = @myapplyid AND

t2.EquipmentID = t1.EquipmentID)

-- D> //更新申请的状态

UPDATE Equipment_MyOfficeApply

SET ApplyID = @NewApplyID, ApplySPUserID =@InputPersonID, ApplyState = 9, ApplyFinishDate =getdate()where

ApplyMyID = @myapplyid

<wbr></wbr>

--ROLLBACK TRANSACTION

commit TRANSACTION

select @RetIno=''

end

select @RetIno '返回值'

return

err_deal:

ROLLBACK TRANSACTION

--SELECT "发生错误" as retInfo

SELECT @RetIno = '发生错误'

GO

第二步>在查询分析器中测试创建的存储过程

declare @MyapplyID int
declare @InputPersonID int
declare @RetIno varchar(100)
set @myapplyid=2001
set @InputPersonID=33
exec ApplyOK @MyapplyID,@InputPersonID,@RetIno output
select @RetIno as '返回值'

第三步>创建调用存储过程的函数

'申请办结处理

Public Function fnApplyOK(ByVal MyapplyID AsString, ByVal InputPersonID As String) As String

Dim myConnection As SqlConnection =GetConnection()

Dim myCommand As New SqlCommand

Dim pa1 As New SqlClient.SqlParameter("@MyapplyID",SqlDbType.Int)

Dim pa2 As NewSqlClient.SqlParameter("@InputPersonID", SqlDbType.Int)

Dim pa3 As New SqlClient.SqlParameter("@RetIno",Data.SqlDbType.VarChar, 100)

pa1.Value = MyapplyID

pa2.Value = InputPersonID

pa3.Direction = ParameterDirection.Output

myCommand.CommandType =CommandType.StoredProcedure

myCommand.Connection = myConnection

myCommand.CommandText = "ApplyOK"

myCommand.Parameters.Add(pa1)

myCommand.Parameters.Add(pa2)

myCommand.Parameters.Add(pa3)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

fnApplyOK =myCommand.Parameters("@RetIno").Value

Finally

myConnection.Close()

End Try

End Function

第四步>调用函数进行申请办结处理

'调用存储过程

Dim rtvalue As String

Dim userid As String

userid = loginvery() '验证登陆

rtvalue = fnApplyOK(lbApplyMyID.Text, userid)

If rtvalue = "" Then

'没有错误

Response.Redirect("Office_ApplyList.aspx?SP=1")

Else

'出错了,弹出出错信息

Common.fndispError(rtvalue)

End If

sqlserver 跨数据库查询

跨数据库服务器查询

By Richard Tsuis, http://richardtsuis.cnblogs.com/ .
This posting is provided "AS IS" with no warranties, and confers norights.

跨数据库服务器查询

方法1：
直接使用UNC网络地址名称指定访问路径（不推荐使用）
例如：

Select col1 from UNCName.DB1.dbo.Table1
Where ID1=(select top 1 ID2 from table2 whereID2=@intUID)

问题：这样的查询可能使用相对指定的方式（NetBIOS或TCP/IP）去进行查询，可能不能体现出数据库引擎的传输优势。而且直接查询也可能是导致问题的原因，实际应该通过存储过程准备好数据集合后再进行查询。

方法2：

通过OPENROWSET函数完成数据库服务器联接和查询操作，准备好数据集合。
当然这种情况下所使用的数据集合是只读的，更新方面可以考虑另外的控制方法，在数据库里可以通过方法1进行，在程序里可以通过数据库连接区别操作：

Declare @emptyContent TABLE
(
recordID uniqueidentifier,
content nvarchar(255)
)

Insert Into @emptyContent (recordID, content)

Select local.recordID, local.content
FROMOPENROWSET('SQLOLEDB','remoteserver';'accountname';'password',
'SELECT recordID, content From database.dbo.table Where content ='''' ') AS local

Select gh.recordID, gh.content
From table gh
Where content = ''
And Not Exists(Select * From @emptyContent ec Where ec.recordID =gh.recordID )