在插入(SQL)中使用相同的值-编程知识-白鹭情

我撰写了一个脚本来用值填充数据库

这是脚本行

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('faithsdispatching@yahoo.com', 1, 0, 1, '', UPPER (EmailAddress), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)

对于NormalizedEmailAddress栏位我需要使用EmailAddress到 Upper

我尝试使用它UPPER (EmailAddress)但收到此错误

第 1 行：列名“EmailAddress”无效。

关于如何使用EmailAddress列的任何想法？

uj5u.com热心网友回复：

我不认为你现在问的是可能的。

作为一种快速的解决方法，您可以将资料插入到临时表中，UPPER()在列上运行命令以一次全部命中它们，然后SELECT INTO从临时表中找到所需的表。

CREATE TABLE #TemporaryAbpUsers
(
    EmailAddress...,
    IsActive...
);

INSERT INTO #AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('faithsdispatching@yahoo.com', 1, 0, 1, '', 'faithsdispatching@yahoo.com', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0);

UPDATE #TemporaryAbpUsers
SET NormalizedEmailAddress = UPPER(NormalizedEmailAddress);

SELECT * INTO AbpUsers
FROM #TemporaryAbpUsers;

uj5u.com热心网友回复：

只需更改UPPER(EmailAddress)为UPPER ('faithsdispatching@yahoo.com')

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('faithsdispatching@yahoo.com', 1, 0, 1, '', UPPER ('faithsdispatching@yahoo.com'), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)

uj5u.com热心网友回复：

为 EmailAddress 使用变量

DECLARE @EmailAddress NVARCHAR(100);

SET @EmailAddress = N'faithsdispatching@yahoo.com';
INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
(@EmailAddress, 1, 0, 1, '', UPPER(@EmailAddress), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0);

对于多个插入，您可以尝试 Insert-From-Select-From-Values。

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
SELECT EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, 
UPPER(EmailAddress) AS NormalizedEmailAddress, 
NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin
FROM (VALUES
  ('faithsdispatching@yahoo.com', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)
, ('hellbendingsextra@funky.net', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQBBBBEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD2Q==', 0) 
, ('yellowriverfishing@asia.org', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQCCCCEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD3Q==', 0) 
) q(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, 
NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)