mysql从一个表导入另一个表,两张结构不同的表，从一张表导入数据到另外一张表 (转)...

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

//插入应用

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 AS

ApplyInOutTypeID,

ApplyReson AS ApplyRemark, ApplyDate, '33' AS

ApplyInputPerson

FROM Equipment_MyOfficeApply

WHERE (ApplyMyID = 2003)

使用存储过程的例子：

第一步>创建存储过程

--

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

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 FROM

Equipment_MyOfficeApply WHERE (ApplyMyID = @MyapplyID))

if @appstate<>8

begin

if @appstate=-1 begin

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

return

end

else if @appstate=9 begin

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

return

end

--- 其他情况

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

return

end

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

select @cgCount =(SELECT COUNT(*) AS cgCount FROM

V_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 AS

ApplyInOutTypeID,

ApplyReson AS ApplyRemark, ApplyDate, '33' AS

ApplyInputPerson

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_MyEquiplist

t2

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

--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 As

String, 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 New

SqlClient.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 no

rights.

跨数据库服务器查询

方法1：

直接使用UNC网络地址名称指定访问路径(不推荐使用)

例如：

Select col1 from UNCName.DB1.dbo.Table1

Where ID1=(select top 1 ID2 from table2 where

ID2=@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

FROM

OPENROWSET('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 )

转自:http://blog.chinahr.com/blog/careybobo/post/16227