如何利用insert into values插入多条数据
insert into values插入多条数据
insert into 表名(字段名1,字段名2)values(值a1,值b1), (值a2,值b2),
例如:
insert into user_info (user_account,user_name,user_age,user_class) values (‘00001′, ‘张三 ‘,‘20′,‘计算机系’), (‘00002′, ‘李四’,‘19′,‘计算机系’);
SQL insert into插入的单行,多行的情况
1、在已有的表中,插入一行数据
a、第一种形式无需指定要插入数据的列名,只需提供被插入的值即可:
INSERT INTO table_name VALUES (value1,value2,value3,…);
insert into subject values (‘语文’)
insert into subject values (‘语文’)
b、第二种形式需要指定列名及被插入的值:
INSERT INTO table_name (column1,column2,column3,…) VALUES (value1,value2,value3,…);
insert into subject(subject_name) values (‘数学’)
insert into subject(subject_name) values (‘数学’)
2、在已有的表中,一次性插入多行行数据
INSERT INTO table_name VALUES (value1,value2,value3,…),(value1,value2,value3,…);
用逗号隔开,括号括起来,加多少行数据就写多少个。要指定列名插入的,参考1.b 的做法。
insert into subject values (‘数学’),(‘英语’)
3、将表1 数据的某些列插入到表2 中去(其中表2是已经创建好,与表1 插入列的属性是一样的):
INSERT INTO 表2(column1,column2) SELECT (column1,column2)FROM 表1
insert into newtable(StuName) select studentname from student
insert into newtable(StuName) select studentname from student
4、将表1 数据的某些列插入到表2 中去(其中表2 是不存在的,在插入的同时创建一个新表):
SELECT column1,column2 INTO 表2 FROM 表1
select studentname,StudentClass into newcreate from Student
select studentname,StudentClass into newcreate from Student
创建表的源码贴在下面了,运行下面的代码创建以后,再运行上面的插入代码,可以更好地理解哦。
CREATE TABLE Student
(
StudentNo int PRIMARY KEY IDENTITY(1,1),
StudentName nvarchar(15) NOT NULL,
StudentAge int DEFAULT ((7)),
StudentSex nvarchar(2) CHECK(StudentSex=N’男’ or StudentSex=N’女’),
StudentClass nvarchar(15)
)
CREATE TABLE Subject
(
SubjectNo INT PRIMARY KEY IDENTITY(1,1),
SubjectName NVARCHAR(15) NOT NULL
)
CREATE TABLE StuResults
(
SR_No INT PRIMARY KEY IDENTITY(1,1),
SR_StudentNo INT,
SR_SubjectNo INT,
SR_Score INT CHECK (SR_Score>=0 AND SR_Score<=120)
)
alter table StuResults
add constraint FK_StudentNo
foreign key (SR_StudentNo) references Student (StudentNo)
alter table StuResults
add constraint FK_Subject
foreign key (SR_SubjectNo) references Subject (SubjectNo)
(
StudentNo int PRIMARY KEY IDENTITY(1,1),
StudentName nvarchar(15) NOT NULL,
StudentAge int DEFAULT ((7)),
StudentSex nvarchar(2) CHECK(StudentSex=N’男’ or StudentSex=N’女’),
StudentClass nvarchar(15)
)
CREATE TABLE Subject
(
SubjectNo INT PRIMARY KEY IDENTITY(1,1),
SubjectName NVARCHAR(15) NOT NULL
)
CREATE TABLE StuResults
(
SR_No INT PRIMARY KEY IDENTITY(1,1),
SR_StudentNo INT,
SR_SubjectNo INT,
SR_Score INT CHECK (SR_Score>=0 AND SR_Score<=120)
)
alter table StuResults
add constraint FK_StudentNo
foreign key (SR_StudentNo) references Student (StudentNo)
alter table StuResults
add constraint FK_Subject
foreign key (SR_SubjectNo) references Subject (SubjectNo)
go
IF EXISTS(SELECT * FROM sysobjects where name = ‘newtable’)
drop table newtable
else
create table newtable
(
StuNo int PRIMARY KEY IDENTITY(1,1),
StuName nvarchar(15) NOT NULL
)
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。