INSERT INTO China (BusinessID,title,Telephone,Address,CityID,Server,x,y,city,categoryname) SELECT commid,commName,commTel,commAddress,cityid,host,x,y,city,'2' FROM community where cityid=500100
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='China'结果Table_NAME COLUMN_NAME China ItemID
/* 查看你的主鍵在哪列 一個表不可能出現兩個主鍵 SQL查询主键信息 */ EXEC sp_pkeys @table_name='表名' --or SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'
EXEC sp_pkeys @table_name='China'Table_QUalifier table_owner table_name column_name Key_seq pk_Name ditu dbo china itemid 1 PK_China
SELECT commid,commName,commTel,commAddress,cityid,host,x,y,city,'2' FROM community where cityid=500100
China ItemID
查看你的主鍵在哪列
一個表不可能出現兩個主鍵
SQL查询主键信息
*/
EXEC sp_pkeys @table_name='表名'
--or
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'
ditu dbo china itemid 1 PK_China
给出表结构,另外,以上SQL语句不包括主键列吧?
另外你也可以手动插入几条语句,看看是不是自动生成了主键.
表结构SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[China]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[China]
GOCREATE TABLE [dbo].[China] (
[ItemId] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[City] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PostCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CName] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[X] [int] NULL ,
[Y] [int] NULL ,
[CType] [int] NULL ,
[IDX] [int] NULL ,
[MAP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Layer] [int] NULL ,
[Cell] [int] NULL ,
[CategoryName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[BusinessID] [int] NULL ,
[Server] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Telephone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Address] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[CityID] [int] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[China] ADD
CONSTRAINT [PK_China] PRIMARY KEY CLUSTERED
(
[ItemId]
) ON [PRIMARY]
GO
所以insert的时候报重复性错误。
取消自增长,再加上自增长,种子恢复正常。
go
insert tb values(100)
insert tb values(200)
select * from tb
/*
id c
----------- -----------
1 100
2 200
*/--重置种子为1
dbcc checkident('tb',reseed,1)
go
--此时再插入数据,id从1自增为2,与表中已有标识列重复,于是报错
insert tb values(300)
/*
消息 2627,级别 14,状态 1,第 1 行
违反了 PRIMARY KEY 约束 'PK__tb__70C8B53F'。不能在对象 'dbo.tb' 中插入重复键。
语句已终止。
*/--重置种子为2,插入正常
dbcc checkident('tb',reseed,2)
go
insert tb values(300)select * from tb
/*
id c
----------- -----------
1 100
2 200
3 300
*/
drop table tb