Home Database Mysql Tutorial SELECT INTO 和 INSERT INTO SELECT 两种表复制语句简单介绍_MySQL

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句简单介绍_MySQL

Jun 01, 2016 pm 01:23 PM
app develop

bitsCN.com Insert是T-sql中常用语句,Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中,经常会遇到需要表复制的情况,如将一个table1的数据的部分字段复制到table2中,或者将整个table1复制到table2中,这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。
1.INSERT INTO SELECT语句
语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1
要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。示例如下:

INSERT INTO SELECT语句复制表数据
--1.创建测试表
create TABLE Table1
(
a varchar(10),
b varchar(10),
c varchar(10),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
a ASC
)
) ON [PRIMARY]
create TABLE Table2
(
a varchar(10),
c varchar(10),
d int,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
a ASC
)
) ON [PRIMARY]
GO
--2.创建测试数据
Insert into Table1 values('赵','asds','90')
Insert into Table1 values('钱','asds','100')
Insert into Table1 values('孙','asds','80')
Insert into Table1 values('李','asds',null)
GO
select * from Table2
--3.INSERT INTO SELECT语句复制表数据
Insert into Table2(a, c, d) select a,c,5 from Table1
GO
--4.显示更新后的结果
select * from Table2
GO
--5.删除测试表
drop TABLE Table1
drop TABLE Table2

2.SELECT INTO FROM语句
语句形式为:SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。示例如下:
SELECT INTO FROM创建表并复制表数据

--1.创建测试表
create TABLE Table1
(
a varchar(10),
b varchar(10),
c varchar(10),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
a ASC
)
) ON [PRIMARY]
GO
--2.创建测试数据
Insert into Table1 values('赵','asds','90')
Insert into Table1 values('钱','asds','100')
Insert into Table1 values('孙','asds','80')
Insert into Table1 values('李','asds',null)
GO
--3.SELECT INTO FROM语句创建表Table2并复制数据
select a,c INTO Table2 from Table1
GO
--4.显示更新后的结果
select * from Table2
GO
--5.删除测试表
drop TABLE Table1
drop TABLE Table2
bitsCN.com

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Unable to save changes to Photos app error in Windows 11 Unable to save changes to Photos app error in Windows 11 Mar 04, 2024 am 09:34 AM

If you encounter the Unable to save changes error while using the Photos app for image editing in Windows 11, this article will provide you with solutions. Unable to save changes. An error occurred while saving. Please try again later. This problem usually occurs due to incorrect permission settings, file corruption, or system failure. So, we’ve done some deep research and compiled some of the most effective troubleshooting steps to help you resolve this issue and ensure you can continue to use the Microsoft Photos app seamlessly on your Windows 11 device. Fix Unable to Save Changes to Photos App Error in Windows 11 Many users have been talking about Microsoft Photos app error on different forums

MS Paint not working properly in Windows 11 MS Paint not working properly in Windows 11 Mar 09, 2024 am 09:52 AM

Microsoft Paint not working in Windows 11/10? Well, this seems to be a common problem and we have some great solutions to fix it. Users have been complaining that when trying to use MSPaint, it doesn't work or open. Scrollbars in the app don't work, paste icons don't show up, crashes, etc. Luckily, we've collected some of the most effective troubleshooting methods to help you resolve issues with Microsoft Paint app. Why doesn't Microsoft Paint work? Some possible reasons why MSPaint is not working on Windows 11/10 PC are as follows: The security identifier is corrupted. hung system

How to connect Apple Vision Pro to PC How to connect Apple Vision Pro to PC Apr 08, 2024 pm 09:01 PM

The Apple Vision Pro headset is not natively compatible with computers, so you must configure it to connect to a Windows computer. Since its launch, Apple Vision Pro has been a hit, and with its cutting-edge features and extensive operability, it's easy to see why. Although you can make some adjustments to it to suit your PC, and its functionality depends heavily on AppleOS, so its functionality will be limited. How do I connect AppleVisionPro to my computer? 1. Verify system requirements You need the latest version of Windows 11 (Custom PCs and Surface devices are not supported) Support 64-bit 2GHZ or faster fast processor High-performance GPU, most

Shazam app not working in iPhone: Fix Shazam app not working in iPhone: Fix Jun 08, 2024 pm 12:36 PM

Having issues with the Shazam app on iPhone? Shazam helps you find songs by listening to them. However, if Shazam isn't working properly or doesn't recognize the song, you'll have to troubleshoot it manually. Repairing the Shazam app won't take long. So, without wasting any more time, follow the steps below to resolve issues with Shazam app. Fix 1 – Disable Bold Text Feature Bold text on iPhone may be the reason why Shazam is not working properly. Step 1 – You can only do this from your iPhone settings. So, open it. Step 2 – Next, open the “Display & Brightness” settings there. Step 3 – If you find that “Bold Text” is enabled

Four recommended AI-assisted programming tools Four recommended AI-assisted programming tools Apr 22, 2024 pm 05:34 PM

This AI-assisted programming tool has unearthed a large number of useful AI-assisted programming tools in this stage of rapid AI development. AI-assisted programming tools can improve development efficiency, improve code quality, and reduce bug rates. They are important assistants in the modern software development process. Today Dayao will share with you 4 AI-assisted programming tools (and all support C# language). I hope it will be helpful to everyone. https://github.com/YSGStudyHards/DotNetGuide1.GitHubCopilotGitHubCopilot is an AI coding assistant that helps you write code faster and with less effort, so you can focus more on problem solving and collaboration. Git

Move photos from old Photos to the new Photos app in Windows 11 Move photos from old Photos to the new Photos app in Windows 11 Mar 10, 2024 am 09:37 AM

This article will guide you on how to migrate photos from Photos Legacy to the new Photos app in Windows 11. Microsoft has introduced a revamped Photos app in Windows 11, giving users a simpler and more feature-rich experience. The new Photos app sorts photos differently than the past PhotosLegacy app. It organizes photos into folders like other Windows files instead of creating albums. However, users still using the Photos Legacy app can easily migrate their photos to the new version of Microsoft Photos. What is Phot

Why does 0xc0000142 error code appear? Why does 0xc0000142 error code appear? Feb 25, 2024 am 11:15 AM

0xc0000142 refers to the error code in Windows systems, and it is usually related to the problem of a program or application not starting or running properly. When a user tries to open a program, the system displays the error message "0xc0000142" and the program cannot continue to run. So, why does the 0xc0000142 error occur? There are many reasons for this error code, here are some common causes and solutions. Corrupted configuration files: The configuration files of some programs may be corrupted, causing

Which AI programmer is the best? Explore the potential of Devin, Tongyi Lingma and SWE-agent Which AI programmer is the best? Explore the potential of Devin, Tongyi Lingma and SWE-agent Apr 07, 2024 am 09:10 AM

On March 3, 2022, less than a month after the birth of the world's first AI programmer Devin, the NLP team of Princeton University developed an open source AI programmer SWE-agent. It leverages the GPT-4 model to automatically resolve issues in GitHub repositories. SWE-agent's performance on the SWE-bench test set is similar to Devin, taking an average of 93 seconds and solving 12.29% of the problems. By interacting with a dedicated terminal, SWE-agent can open and search file contents, use automatic syntax checking, edit specific lines, and write and execute tests. (Note: The above content is a slight adjustment of the original content, but the key information in the original text is retained and does not exceed the specified word limit.) SWE-A

See all articles