背景:
在运维工作当中常常需要使用零时表进行储存一些信息
1.建表语句
CREATE TABLE [dbo].[stu1] (
[sno] int NOT NULL ,
[name] varchar(255) NULL
)
INSERT INTO [dbo].[stu1] ([sno], [name]) VALUES (N'1', N'tjr1')
INSERT INTO [dbo].[stu1] ([sno], [name]) VALUES (N'2', N'tjr2')
INSERT INTO [dbo].[stu1] ([sno], [name]) VALUES (N'3', N'frfr')
CREATE TABLE [dbo].[stu2] (
[sno] int NOT NULL ,
[age] int NULL
)
INSERT INTO [dbo].[stu2] ([sno], [age]) VALUES (N'1', N'1')
INSERT INTO [dbo].[stu2] ([sno], [age]) VALUES (N'2', N'2')
INSERT INTO [dbo].[stu2] ([sno], [age]) VALUES (N'3', N'3')
INSERT INTO [dbo].[stu2] ([sno], [age]) VALUES (N'4', N'4')
如何把两张表变成如下结果:
SELECT s2.sno,s2.age,s1.name FROM [dbo].[stu2] s2,stu1 s1
where s1.sno=s2.sno
2.into方式插入零时表当中:
SELECT s2.sno,s2.age,s1.name
into #test
FROM [dbo].[stu2] s2,stu1 s1
where s1.sno=s2.sno
SELECT * from #test
3.使用update方式插入零时表
drop table #stu
create table #stu(
sno int,
name varchar(50),
age int
)
INSERT into #stu(sno,name)
SELECT sno,name
from stu1
UPDATE s set s.age=s2.age from #stu s
LEFT JOIN stu2 s2 on s.sno=s2.sno
SELECT * from #stu