Insert multiple values using INSERT INTO (SQL Server 2005)+

Posted in :

結論:MSSQL 2008+ 可以直接用逗號隔開 VALUES, 當然 MYSQL 也支援這個寫法。


The syntax you are using is new to SQL Server 2008:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test'),(1001,N'test2')

For SQL Server 2005, you will have to use multiple INSERT statements:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test')

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1001,N'test2')

One other option is to use UNION ALL:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *