如何在Sql Server 2012中使用WHEN不退出时插入新记录 [英] How Do I Insert New Record Using WHEN Not Exits In Sql Server 2012

问题描述

大家好，



我试图将表（UserMgmt）中的新记录插入另一个表（UserSignInHistory），这两个表中的字段是相同的。我想每天插入一次记录。这是我的代码：

Hello everyone,

I am trying to insert new records from table (UserMgmt) to another table (UserSignInHistory), the fields in these two tables are same. i want each record to be inserted once per day. This is my code:

INSERT INTO UserSignInHistory 
		SELECT [Staffid],[Username],[Surname],[FirstName],[Branch],[UserRoles],[DateCreated],[SignInDate],[IsActive],GETDATE()
		FROM [UserMgmt] um
		WHERE
        NOT EXISTS (SELECT * FROM UserSignInHistory ss
		
		WHERE ((um.SignInDate <> '2015-05-05' OR um.SignInDate IS NULL) AND um.UserRoles = 'RCO'))

但我是出现此错误：表'UserSignInHistory'中标识列的显式值只能在使用列列表且IDENTITY_INSERT为ON时指定。



i已通过代码，我无法修复此错误，请任何人可以协助吗？

but i am having this error: An explicit value for the identity column in table 'UserSignInHistory' can only be specified when a column list is used and IDENTITY_INSERT is ON.

i have gone through the code, i couldn't fix this error, please can anyone assist?

推荐答案

错误消息实际上告诉您出错了什么。您正在将值强制转换为标识字段，该字段通常会像大多数主键字段一样生成它自己的值。



您需要检查表的结构以识别身份字段然后安排你的SQL如下：

The error message actually tells you what's wrong. You are forcing a value into an identity field that usually generates it's own value as most primary key fields do.

You will need to check the table's structure to identify the "identity field" and then arrange you SQL as this:

INSERT INTO UserSignInHistory ([Username],/*LIST OF FIELDS EXCLUDING IDENTITY FIELD*/)
		SELECT [Username],/*LIST OF FIELDS EXCLUDING IDENTITY FIELD*/
		FROM [UserMgmt] um
		WHERE
        NOT EXISTS (SELECT * FROM UserSignInHistory ss
		
		WHERE ((um.SignInDate <> '2015-05-05' OR um.SignInDate IS NULL) AND um.UserRoles = 'RCO'))

这篇关于如何在Sql Server 2012中使用WHEN不退出时插入新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！