SQL表单条数据插入与多条数据插入

-创建用户表
CREATE TABLE USERS(
UId INT IDENTITY(1,1) NOT NULL,
UName VARCHAR(20) NOT NULL,
UPassword VARCHAR(20) NOT NULL,
UAge INT,
USex BIT DEFAULT 0 NOT NULL,
)
SELECT * FROM USERS;
GO

结果：

-插入数据的方法

一条数据插入

--插入一条记录INSERT INTO <表名> (列名) values (值)
INSERT INTO USERS(UName,UPassword,UAge,USex) values('Zhangsan','Zhangsanpassword12',12,DEFAULT);
SELECT * FROM USERS;
Go

--多条数据插入

--多条数据插入方法一：INSERT INTO <表名>(列名) SELECT <列名> UNION,SELECT <列名> UNION....; 注意最后一项不要UNION
INSERT INTO USERS (UName,UPassword,UAge,USex) SELECT '李四','lisi',13,0 UNION SELECT '王五','wangwu',15,1;
SELECT * FROM USERS;
GO

-多条数据插入方法二：INSERT INTO <表名> (列名) SELECT <列名> FROM <源表名>;
INSERT INTO USERS (UName,UPassword,UAge,USex) SELECT UName,UPassword,UAge,USex FROM USERS;

--多条数据插入方法三：SELECT <列名> INTO 新表名 FROM <源表名>;注意：是创建新表，把原有表数据复制并创建新表，如果表存在不能创建，该语句只能执行一次。
--如果创建标识属性字段，用IDENTITY(数据类型,标识种子,标识增长量)
SELECT * FROM VIPS;--表不存在
SELECT UName as VName,UPassword as VPassword INTO VIPS FROM USERS;
SELECT * FROM VIPS;
GO

SELECT UName as VName,UPassword as VPassword INTO VIPS FROM USERS;
SELECT * FROM SUPERVIPS;--表不存在
SELECT IDENTITY(INT,1,1) AS SVid,UName as SVName,UPassword as SVPassword INTO SUPERVIPS FROM USERS;
SELECT * FROM SUPERVIPS;
GO