MSSQL insert into and other data update operations

Source: Internet
Author: User

If object_id ('test', 'U') is not null drop Table Test
Create Table Test
(
TID int identity (1, 1) primary key,
Tname varchar (32)
)
Go

-- Single row insert
Insert into test (tname) values ('chensirbbk ');

 

-- Insert multiple rows
-- Method 1:
Insert into test (tname)
Select 'name1'
Union all select 'name2'
Union all select 'name3 ';

-- Method 2:
Insert into test (tname)
Values ('name4 '),
('Name5 '),
('Name6 ');

I also want to introduce some tips for inserting table records in SQL statements.
1. insert records and query results
Select * from (values
('Name7 '),
('Name8 '),
('Name9 ')
) As T (tname );

 

2. Insert Exec: place the query results of the stored procedure into the insert statement.
Insert into test (tname)
Exec proc_testsel @ tname = 'xxxx'; -- stored procedure omitted [may not be the current table]

 

3. Insert select inserts the query result into an existing table.
If object_id ('test1', 'U') is not null drop table test1
Create Table test1
(
TID int,
Tname varchar (32)
)
Go

Insert into test1 (TID, tname) Select tid, tname from test;

 

4. If select into exists in special 2, the basic structure column name and Data Type of the table will be copied, which can be null, identity attribute and data.
Two things won't be copied: Constraints, indexes, and triggers.
Select * into Test2 from test1

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.