The typical scenarios of using "Insert" in T-sql

1.The most common one
INSERT INTO V1 (col1, col2)  VALUES ('Row 1',1);

2.Sometimes you just need to insert a default row
INSERT INTO T1 DEFAULT VALUES;

3.What if you have a Identity column, and you still want to insert a row with you own value of that column?
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2)  VALUES (-99, 'Explicit identity value');

4.What if the table that you want to insert has a uniqueidentifier column?
CREATE TABLE dbo.T1
(
    column_1 int IDENTITY,
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2)  VALUES (NEWID());

------------------------------------------------------------------------------------------
-- above are all the use of "INSERT INTO". Now let try the "INSERT...SELECT"
------------------------------------------------------------------------------------------

5.most common "insert... select"
insert T1
select col1, col2 from T2

6.like "select into", you can only set value for part of the columns
insert test12(ItemID)
select ItemID
from test11 where ItemID=-6188872580015720368

7.you can use EXEC if you want, to run a sp or string
insert T1 exec('select col1, col2 from T2') -- run a query string
insert T1 exec sp_YouSPName  -- run a sp

8.two ways of top insert. I prefer the second one, and you?
insert top (10) into test12 select * from test11
insert test12 select top 10 * from test11

-----------------------------------------------------------------------------
--For above senarios, you all need to create a table before you insert rows into it
--now let's see something different
--------------------------------------------------------------------------------
9.quick copy
select * into T2 from T1  -- you don't need to create T2 before it

10.quick create a empty table using the same schema with a existed table
select * into T2 from T1 where 1=0

--------------------------------------------------------------------------
--What if we want to get some thing out of the new-inserted rows?
--Like the value of a indentify column, or the value of some computed column?
--------------------------------------------------------------------------------
11. A simple example of 'Output'
--ScrapReasonID is a IDENTITY column in Table 'ScrapReason'
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
GO
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar

VALUES (N'Operator error', GETDATE());

转载于:https://www.cnblogs.com/xingyukun/archive/2008/04/12/1149829.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值