怎样用SQL向数据库中批量的插入数据，主键是随机生成的

Question

如题所述

Answer 1

代码如下:
--创建测试表
CREATE TABLE [Identity](
Id INT IDENTITY(1,2) NOT NULL PRIMARY KEY,--种子的起始值1，步长2
Number VARCHAR(20) UNIQUE NOT NULL,
Name VARCHAR(20) NOT NULL,
Password VARCHAR(20) DEFAULT(123),
Description VARCHAR(40) NULL
)
--插入记录
INSERT INTO [Identity](Number,Name,Description) VALUES('001','1st','Id=1,因为起始值1')
INSERT INTO [Identity](Number,Name,Description) VALUES('002','2nd','Id=3,因为起始值1，步长2')
INSERT INTO [Identity](Number,Name,Description) VALUES('003','3rd','Id=5,由于字符长度超长，报错插入失败，造成此Id产生后被放弃')
INSERT INTO [Identity](Number,Name,Description) VALUES('004','4th','Id=7 not 5,因为第三条记录插入失败')
--检索记录，查看结果
SELECT * FROM [Identity]

Answer 2

使用数据类型uniqueidentifier最合适了
--创建表
CREATE
TABLE
[A_Tmp]
(
[Primary_ID]
[uniqueidentifier]
NOT
NULL
,
[Col1]
[VarChar]
(10)
COLLATE
Chinese_PRC_CI_AS
NULL,
[Col2]
[VarChar]
(10)
COLLATE
Chinese_PRC_CI_AS
NULL,
[Col3]
[VarChar]
(10)
COLLATE
Chinese_PRC_CI_AS
NULL
CONSTRAINT
[PK_A_Tmp]
PRIMARY
KEY
NONCLUSTERED
(
[Primary_ID]
)
WITH
FILLFACTOR
=
90
ON
[PRIMARY]
)
ON
[PRIMARY]
GO
DECLARE
@MyID
uniqueidentifier
DECLARE
@INT
AS
INT
SET
@INT
=
0
WHILE
@INT
<
500000
--这里设置需要插入多少行
BEGIN
SET
@INT
=
@INT
+
1
SET
@MyID
=
NEWID()
INSERT
INTO
A_Tmp
(Primary_ID,
Col1,
Col2,
Col3)
SELECT
@MyID,
'',
'',
''
END