SQL的INSERT INTO的几种写法

SQL的INSERT INTO的几种写法

先创建两个表 Test和NameList。
CREATE TABLE Test(
test_ID int IDENTITY(1,1),
test_Name nvarchar(30),
test_Sex nvarchar(2)
) ON [PRIMARY]

CREATE TABLE NameList(
NameList_ID int IDENTITY(1,1),
NameList_Name nvarchar(30),
NameList_Sex nvarchar(2)
) ON [PRIMARY]

1.INSERT INTO Test(test_Name,test_Sex)VALUES(‘小明’,‘男’)
2.INSERT INTO Test VALUES(‘小明’,‘男’)
3.批量导入
–只支持SQL2005以上
INSERT INTO Test VALUES(‘小白’,‘女’),(‘小青’,‘女’),(‘雷迪’,‘男’),(‘龙翔’,‘男’)
4.用SELECT的方法两种写法以下都可以
INSERT INTO Test(test_Name,test_Sex) SELECT ‘小白’,‘女’
INSERT INTO Test SELECT ‘小白’,‘女’
5.批量导入，两种写法。
INSERT INTO Test(test_Name,test_Sex) SELECT ‘小白’,‘女’ UNION ALL SELECT ‘小青’,‘女’ UNION ALL SELECT ‘雷迪’,‘男’
INSERT INTO Test SELECT ‘小白’,‘女’ UNION ALL SELECT ‘小青’,‘女’ UNION ALL SELECT ‘雷迪’,‘男’
6.查询导入
INSERT INTO NameList SELECT test_Name,test_Sex FROM Test