您现在的位置: 365建站网 > 365学习 > The typical scenarios of using Insert in T-sql

The typical scenarios of using Insert in T-sql

文章来源:365jz.com     点击数:73    更新时间:2009-09-20 08:58   参与评论

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());

如对本文有疑问,请提交到交流论坛,广大热心网友会为你解答!! 点击进入论坛


发表评论 (73人查看0条评论)
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
用户名: 验证码: 点击我更换图片
最新评论
------分隔线----------------------------