IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Manager') AND type in (N'U')) create table Manager() insert into Manager() values() else insert into Manager() values()
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Manager') AND type in (N'U')) create table Manager() else delete from Manager insert into Manager() values()
if exists(select * from sysobjects where xtype='u' and name='Manager') begin --truncate table Manager --insert Manager (...) values(...) end else begin --create table Manager(...) --insert Manager (...) values(...) end
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON if exists (select * from sysobjects where id = object_id(N'[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [student] ; CREATE TABLE [dbo].[student]( [id] [int] IDENTITY(1,1) NOT NULL, [student_id] [nvarchar](50) NULL, [name] [nvarchar](16) NULL, [psw] [nvarchar](32) NULL CONSTRAINT [DF_student_psw] DEFAULT ('c4ca4238a0b923820dcc509a6f75849b'), [sex] [bit] NULL CONSTRAINT [DF_student_sex] DEFAULT ('True'), [classid] [int] NULL, [zyname] [nvarchar](100) NULL, CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED ( [id] ASC ) --WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]这里表名是student。
再存储过程中执行 if exists(select * from dbo.sysobjects where id= object_id('N[dbo].[Manager]') drop table dbo.Manager 插入数据
if exists(select * from sysobjects where name='a') begin drop table a -- 删除后就不能插入了 不知道楼主什么意思 end else begin create table A(Uname varchar(10),Uid int) end
你这个可以运行数据库的脚本生成功能就可以知道怎么实现了,以下是对表t_Equip的脚本生成if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_Equip]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_Equip]
这个你可以写成一个存储过程 也可以一个一个的通过SQL来判断给你个例子 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yourtablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[yourtablename] CREATE TABLE [dbo].[yourtablename] ( [ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , .............你的列 ) ON [PRIMARY]然后就是Insert语句了
create table Manager()
insert into Manager() values()
else
insert into Manager() values()
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Manager') AND type in (N'U'))
create table Manager()
else
delete from Manager
insert into Manager() values()
if exists(select * from sysobjects where xtype='u' and name='Manager')
begin
--truncate table Manager
--insert Manager (...) values(...)
end
else
begin
--create table Manager(...)
--insert Manager (...) values(...) end
SET QUOTED_IDENTIFIER ON
if exists (select * from sysobjects where id = object_id(N'[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [student]
;
CREATE TABLE [dbo].[student](
[id] [int] IDENTITY(1,1) NOT NULL,
[student_id] [nvarchar](50) NULL,
[name] [nvarchar](16) NULL,
[psw] [nvarchar](32) NULL CONSTRAINT [DF_student_psw] DEFAULT ('c4ca4238a0b923820dcc509a6f75849b'),
[sex] [bit] NULL CONSTRAINT [DF_student_sex] DEFAULT ('True'),
[classid] [int] NULL,
[zyname] [nvarchar](100) NULL,
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
) --WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]这里表名是student。
if exists(select * from dbo.sysobjects where id=
object_id('N[dbo].[Manager]')
drop table dbo.Manager
插入数据
try
{
DropTable();
}
catch{}
CreateTable();就这种情况而言,如果是个别,不一定非得判断。。
if exists(select * from sysobjects where name='a')
begin
drop table a -- 删除后就不能插入了 不知道楼主什么意思
end
else
begin
create table A(Uname varchar(10),Uid int)
end
drop table [dbo].[t_Equip]
也可以一个一个的通过SQL来判断给你个例子
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yourtablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yourtablename]
CREATE TABLE [dbo].[yourtablename] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
.............你的列
) ON [PRIMARY]然后就是Insert语句了
所以建议用truncate table,而不是drop table按楼主的意思,可以直接删除表,然后创建表,再插入数据了
不知还有什么方法吗?我的ORACLE表 select * from sysobjects where name='a' 这个在ORACLE里面貌似不能执行。。
//执行这个SQL文,具体代码我不写了,你可以用DataSet DataReader等方式返回值
int count = 你的结果
if(count > 0)
{
//表存在,删除之
sql = "drop table 大写表名";
//执行这个SQL,具体代码我不写了
}sql = "create table.........";
//执行这个sql,具体代码我不写了
sql = "insert into ....";
//执行这个SQL,具体代码我不写了完毕~~~~~
执行SQL的代码自己写吧
当然可以,先试着把表给drop,如果表存在,那么肯定可以drop,如果不存在,会有异常,但try捕捉了。
接着往下就可以Create了,不用管之前数据库是否有该表(有的话就在前面drop了),创建就行。