数据库中已存在对象Exception
本文关键字:对象 Exception 存在 数据库 | 更新日期: 2023-09-27 18:24:34
我的代码出了什么问题。。。我试了好几种方法,但一次又一次地出现同样的错误。
有什么帮助吗??代码为:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[SubDomainName].ToString());
conn.Open();
string temptable = "CREATE TABLE [dbo].[Tmptablenew]([TicketID] [int] NULL,[TicketDescription][varchar](max) NULL,[TicketAssignedTo] [varchar](100) NULL,[TicketCreatedDate] [datetime] NULL,[TicketStatus][varchar](50),CRMConnectionID [int] NULL,[TicketUpdatedDate] [Datetime] NULL,img [varchar](500) NULL)";
SqlCommand cmd = new SqlCommand(temptable, conn);
cmd.ExecuteNonQuery();
SqlCommand cmmd = new SqlCommand("select * from Tickets", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
DataTable dt1 = new DataTable("dt1");
adapter.Fill(dt1);
cmmd.ExecuteNonQuery();
//BulkCopy the data in the DataTable to the temp table
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
{
bulk.DestinationTableName = "Tmptablenew";
bulk.WriteToServer(result);
conn.Close();
}
conn.Open();
string mergeSql = "merge into Tickets as Target " +
"using Tmptablenew as Source " +
"on " +
"Target.TicketID= Source.TicketID " +
"and Target.CRMConnectionID = Source.CRMConnectionID " +
"when not matched then " +
"insert (TicketID,TicketDescription,TicketAssignedTo,TicketCreatedDate,TicketStatus,CRMConnectionID,TicketUpdatedDate,img) values (Source.TicketID,Source.TicketDescription,Source.TicketAssignedTo,Source.TicketCreatedDate,Source.TicketStatus,Source.CRMConnectionID,Source.TicketUpdatedDate,Source.img);";
string mergesql1 = "Update Tickets SET TicketDescription=S.TicketDescription, TicketAssignedTo = S.TicketAssignedTo, TicketStatus = S.TicketStatus,TicketUpdatedDate = S.TicketUpdatedDate,img = S.img FROM Tickets t JOIN Tmptablenew AS S ON t.TicketID = S.TicketID and T.CRMConnectionID = S.CRMConnectionID";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
cmmd.CommandText = mergesql1;
cmmd.ExecuteNonQuery();
cmd.CommandText = "drop table Tmptablenew";
cmd.ExecuteNonQuery();
//Clean up the temp table
conn.Close();
您正在创建表Tmptablenew
,看起来这个表已经存在于数据库中。
我认为,您正在尝试创建一个临时表,在本例中,将其创建为MySQL的CREATE TEMPORARY TABLE
,或将表名#Tmptablenew
用于MS SQL。在这种情况下,当会话关闭时,它也会自动删除。
编辑
因此,如果您使用MySQL,则必须更改string temptable = "CREATE TEMPORARY TABLE [dbo].[Tmptablenew]([TicketID]...
等(根据您的问题标签)。或者,如果它是MSSQL(正如我所怀疑的),那么string temptable = "CREATE TABLE [dbo].[#Tmptablenew]([TicketID]...
等等(在这种情况下,您应该在代码中的任何地方将其命名为#Tmptablenew
)。