æ们éè¿SQLèæ¬æ¥æå ¥æ°æ®ï¼å¸¸è§å¦ä¸åç§æ¹å¼ã
æ¹å¼ä¸ï¼ä¸æ¡ä¸æ¡æå ¥ï¼æ§è½æå·®ï¼ä¸å»ºè®®ä½¿ç¨ã
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'çæ 1段',160);INSERT INTO Product(Id,Name,Price) VALUES(newid(),'çæ 2段',260);
......
æ¹å¼äºï¼insert bulk
è¯æ³å¦ä¸ï¼
BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }
WITH (
[ BATCHSIZE [ = batch_size ] ],
[ CHECK_CONSTRAINTS ],
[ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ],
[ DATAFILETYPE [ = 'char' | 'native'| 'widechar' | 'widenative' ] ],
[ FIELDTERMINATOR [ = 'field_terminator' ] ],
[ FIRSTROW [ = first_row ] ],
[ FIRE_TRIGGERS ],
[ FORMATFILE = 'format_file_path' ],
[ KEEPIDENTITY ],
[ KEEPNULLS ],
[ KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ],
[ LASTROW [ = last_row ] ],
[ MAXERRORS [ = max_errors ] ],
[ ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ],
[ ROWS_PER_BATCH [ = rows_per_batch ] ],
[ ROWTERMINATOR [ = 'row_terminator' ] ],
[ TABLOCK ],
)
ç¸å ³åæ°è¯´æï¼
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ] --BATCHSIZEæ令æ¥è®¾ç½®å¨å个äºå¡ä¸å¯ä»¥æå
¥å°è¡¨ä¸çè®°å½çæ°é
[ [ , ] CHECK_CONSTRAINTS ] --æå®å¨å¤§å®¹é导å
¥æä½æé´ï¼å¿
é¡»æ£æ¥ææ对ç®æ 表æè§å¾ç约æãè¥æ²¡æ CHECK_CONSTRAINTS é项ï¼åææ CHECK å FOREIGN KEY 约æé½å°è¢«å¿½ç¥ï¼å¹¶ä¸å¨æ¤æä½ä¹å表ç约æå°æ 记为ä¸å¯ä¿¡ã
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] --æå®è¯¥æ°æ®æ件ä¸æ°æ®ç代ç 页
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] --æå® BULK INSERT 使ç¨æå®çæ°æ®æ件类åå¼æ§è¡å¯¼å
¥æä½ã
[ [ , ] FIELDTERMINATOR = 'field_terminator' ] --æ è¯åéå
容ç符å·
[ [ , ] FIRSTROW = first_row ] --æå®è¦å è½½ç第ä¸è¡çè¡å·ãé»è®¤å¼æ¯æå®æ°æ®æ件ä¸ç第ä¸è¡
[ [ , ] FIRE_TRIGGERS ] --æ¯å¦å¯å¨è§¦åå¨
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ] --æå®å¯¼å
¥æ°æ®æ件ä¸çæ è¯å¼ç¨äºæ è¯å
[ [ , ] KEEPNULLS ] --æå®å¨å¤§å®¹é导å
¥æä½æé´ç©ºååºä¿çä¸ä¸ªç©ºå¼ï¼èä¸æå
¥ç¨äºåçä»»ä½é»è®¤å¼
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ] --æå®è¦å è½½çæåä¸è¡çè¡å·
[ [ , ] MAXERRORS = max_errors ] --æå®å
许å¨æ°æ®ä¸åºç°çæå¤è¯æ³é误æ°ï¼è¶
è¿è¯¥æ°éåå°åæ¶å¤§å®¹é导å
¥æä½ã
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] --æå®æ°æ®æ件ä¸çæ°æ®å¦ä½æåº
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ] --æ è¯åéè¡ç符å·
[ [ , ] TABLOCK ] --æå®ä¸ºå¤§å®¹é导å
¥æä½æç»æ¶é´è·åä¸ä¸ªè¡¨çº§é
[ [ , ] ERRORFILE = 'file_name' ] --æå®ç¨äºæ¶éæ ¼å¼æ误ä¸ä¸è½è½¬æ¢ä¸º OLE DB è¡éçè¡çæ件ã
)]
æ¹å¼ä¸ï¼INSERT INTO xx select...
INSERT INTO Product(Id,Name,Price) SELECT NEWID(),'çæ 1段',160
UNION ALL
SELECT NEWID(),'çæ 2段',180 UNION ALL......
æ¹å¼åï¼æ¼æ¥SQL
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'çæ 1段',160)
,(newid(),'çæ 2段',260)
......