下面一个SP中: IF EXISTS (SELECT 1 FROM @UsedTables WHERE TableName = N'SampleAudit')
BEGIN
--得到复制行
SELECT * INTO dbo.#TempTable_tg_SampleRevision
FROM dbo.SampleAudit sa
WHERE EXISTS
(
SELECT 1
FROM @UsedTables u
WHERE u.TableName = N'SampleAudit' and sa.SA_RowID = u.Guid
);
--产生新老RowID对照表
SELECT NewID() NewRowID, SA_RowID OldRowID INTO dbo.#RowID_Contrast
FROM dbo.#TempTable_tg_SampleRevision;
--更新复制行
UPDATE dbo.#TempTable_tg_SampleRevision
SET SR_RowID = @SR_RowID, SA_RowID = c.NewRowID
FROM dbo.#TempTable_tg_SampleRevision t INNER JOIN
dbo.#RowID_Contrast c ON c.OldRowID = t.SA_RowID;
--添加到数据表
INSERT INTO dbo.SampleAudit
SELECT * FROM dbo.#TempTable_tg_SampleRevision;
DROP TABLE dbo.#TempTable_tg_SampleRevision;
--添加项目日志行
INSERT INTO dbo.ProjectLog
(
PL_RowID, PJ_RowID, SR_RowID, TableName, Guid,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
)
SELECT
NewID(), PJ_RowID, @SR_RowID, TableName, rc.NewRowID,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
FROM dbo.ProjectLog pl INNER JOIN
dbo.#RowID_Contrast rc ON rc.OldRowID = pl.Guid
WHERE pl.PJ_RowID = @PJ_RowID and
pl.SR_RowID = @Previous_SR_RowID and
pl.TableName = N'SampleAudit';
DROP TABLE dbo.#RowID_Contrast;
END;
--样品审核发送
IF EXISTS (SELECT 1 FROM @UsedTables WHERE TableName = N'SampleAuditSend')
BEGIN
--下面一句出错:
服务器: 消息 2714,级别 16,状态 1,过程 tg_SampleRevision,行 150
There is already an object named '#TempTable_tg_SampleRevision' in the database.
但是上面我已经Drop了啊? 怎么改啊?
--得到复制行
SELECT * INTO dbo.#TempTable_tg_SampleRevision
FROM dbo.SampleAuditSend sa
WHERE EXISTS
(
SELECT 1
FROM @UsedTables u
WHERE u.TableName = N'SampleAuditSend' and sa.SA_RowID = u.Guid
)
--产生新老RowID对照表
SELECT NewID() NewRowID, SA_RowID OldRowID INTO dbo.#RowID_Contrast
FROM dbo.#TempTable_tg_SampleRevision;
--更新复制行
UPDATE dbo.#TempTable_tg_SampleRevision
SET SA_RowID = c.NewRowID
FROM dbo.#TempTable_tg_SampleRevision t INNER JOIN
dbo.#RowID_Contrast c ON c.OldRowID = t.SA_RowID;
--添加到数据表
INSERT INTO dbo.SampleAuditSend
SELECT * FROM dbo.#TempTable_tg_SampleRevision;
DROP TABLE dbo.#TempTable_tg_SampleRevision;
--添加项目日志行
INSERT INTO dbo.ProjectLog
(
PL_RowID, PJ_RowID, SR_RowID, TableName, Guid,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
)
SELECT
NewID(), PJ_RowID, @SR_RowID, TableName, rc.NewRowID,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
FROM dbo.ProjectLog pl INNER JOIN
dbo.#RowID_Contrast rc ON rc.OldRowID = pl.Guid
WHERE pl.PJ_RowID = @PJ_RowID and
pl.SR_RowID = @Previous_SR_RowID and
pl.TableName = N'SampleAuditSend';
DROP TABLE dbo.#RowID_Contrast;
END;
BEGIN
--得到复制行
SELECT * INTO dbo.#TempTable_tg_SampleRevision
FROM dbo.SampleAudit sa
WHERE EXISTS
(
SELECT 1
FROM @UsedTables u
WHERE u.TableName = N'SampleAudit' and sa.SA_RowID = u.Guid
);
--产生新老RowID对照表
SELECT NewID() NewRowID, SA_RowID OldRowID INTO dbo.#RowID_Contrast
FROM dbo.#TempTable_tg_SampleRevision;
--更新复制行
UPDATE dbo.#TempTable_tg_SampleRevision
SET SR_RowID = @SR_RowID, SA_RowID = c.NewRowID
FROM dbo.#TempTable_tg_SampleRevision t INNER JOIN
dbo.#RowID_Contrast c ON c.OldRowID = t.SA_RowID;
--添加到数据表
INSERT INTO dbo.SampleAudit
SELECT * FROM dbo.#TempTable_tg_SampleRevision;
DROP TABLE dbo.#TempTable_tg_SampleRevision;
--添加项目日志行
INSERT INTO dbo.ProjectLog
(
PL_RowID, PJ_RowID, SR_RowID, TableName, Guid,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
)
SELECT
NewID(), PJ_RowID, @SR_RowID, TableName, rc.NewRowID,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
FROM dbo.ProjectLog pl INNER JOIN
dbo.#RowID_Contrast rc ON rc.OldRowID = pl.Guid
WHERE pl.PJ_RowID = @PJ_RowID and
pl.SR_RowID = @Previous_SR_RowID and
pl.TableName = N'SampleAudit';
DROP TABLE dbo.#RowID_Contrast;
END;
--样品审核发送
IF EXISTS (SELECT 1 FROM @UsedTables WHERE TableName = N'SampleAuditSend')
BEGIN
--下面一句出错:
服务器: 消息 2714,级别 16,状态 1,过程 tg_SampleRevision,行 150
There is already an object named '#TempTable_tg_SampleRevision' in the database.
但是上面我已经Drop了啊? 怎么改啊?
--得到复制行
SELECT * INTO dbo.#TempTable_tg_SampleRevision
FROM dbo.SampleAuditSend sa
WHERE EXISTS
(
SELECT 1
FROM @UsedTables u
WHERE u.TableName = N'SampleAuditSend' and sa.SA_RowID = u.Guid
)
--产生新老RowID对照表
SELECT NewID() NewRowID, SA_RowID OldRowID INTO dbo.#RowID_Contrast
FROM dbo.#TempTable_tg_SampleRevision;
--更新复制行
UPDATE dbo.#TempTable_tg_SampleRevision
SET SA_RowID = c.NewRowID
FROM dbo.#TempTable_tg_SampleRevision t INNER JOIN
dbo.#RowID_Contrast c ON c.OldRowID = t.SA_RowID;
--添加到数据表
INSERT INTO dbo.SampleAuditSend
SELECT * FROM dbo.#TempTable_tg_SampleRevision;
DROP TABLE dbo.#TempTable_tg_SampleRevision;
--添加项目日志行
INSERT INTO dbo.ProjectLog
(
PL_RowID, PJ_RowID, SR_RowID, TableName, Guid,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
)
SELECT
NewID(), PJ_RowID, @SR_RowID, TableName, rc.NewRowID,
[Action], Step, SampleID, [Date], Operator, AddDate,
ChineseDescript, EnglishDescript
FROM dbo.ProjectLog pl INNER JOIN
dbo.#RowID_Contrast rc ON rc.OldRowID = pl.Guid
WHERE pl.PJ_RowID = @PJ_RowID and
pl.SR_RowID = @Previous_SR_RowID and
pl.TableName = N'SampleAuditSend';
DROP TABLE dbo.#RowID_Contrast;
END;
*****************************************************************************
欢迎使用CSDN论坛阅读器 : CSDN Reader(附全部源代码)
http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tg_SampleRevision]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop Trigger [dbo].[tg_SampleRevision] --样品版本
GOCreate Trigger [dbo].[tg_SampleRevision]
On [dbo].[SampleRevision]
For insert, update, delete
As
Set NoCount On;SELECT * INTO dbo.#temp FROM dbo.SampleStep;
DROP TABLE dbo.#temp;/*下面一句话出错:
服务器: 消息 2714,级别 16,状态 1,过程 tg_SampleRevision,行 11
There is already an object named '#temp' in the database.*/SELECT * INTO #temp FROM dbo.[User];
DROP TABLE #temp;SET NOCOUNT OFF;
DROP TABLE dbo.#temp;
SELECT * INTO #temp FROM dbo.[User];
DROP TABLE #temp;
这四句一句句执行没问题
服务器: 消息 2714,级别 16,状态 1,过程 tg_SampleRevision,行 11
There is already an object named '#temp' in the database.*/
===============================================
临时表已存在(数据库中已存在#temp的对象)If Object_Id('Tempdb.dbo.#temp') Is Not Null
drop table Tempdb.dbo.#tempSELECT * INTO #temp FROM dbo.[User];
DROP TABLE #temp;
试试加上前缀tempdb..
另外
drop 后加go
If Object_Id('Tempdb.dbo.#temp') Is Not Null
drop table Tempdb.dbo.#temp仍然出错:服务器: 消息 2714,级别 16,状态 1,过程 tg_SampleRevision,行 14
There is already an object named '#temp' in the database.
Database name 'Tempdb' ignored, referencing object in tempdb.
===
SP/TG中可以用Go吗?
drop table #temp
select top 10 * into #temp from master..sysobjectsselect * from #temp
SELECT * INTO dbo.#temp FROM dbo.SampleStep;
DROP TABLE dbo.#temp;
SELECT * INTO #temp FROM dbo.[User];
DROP TABLE #temp;這4句同時執行的時候,SQL會發現有2次select * into #temp... 所以會報錯/*there is...*/
加個go
應該可以搞定了吧
SELECT * INTO dbo.#temp FROM dbo.SampleStep
DROP TABLE dbo.#temp
go
SELECT * INTO #temp FROM dbo.[User]
DROP TABLE #temp
go
YiZhiNet(九斤半) ( 一星(中级)) 你的那段没问题,但你加一段就应该有问题了
象这样SELECT * INTO dbo.#temp FROM dbo.SampleStep;
DROP TABLE dbo.#temp;
SELECT * INTO #temp FROM dbo.[User];
DROP TABLE #temp;就编译不过~