ExcelÊý¾Ýµ¼ÈëSql Server³öÏÖNull
ÔÚExcelÖУ¬ÎÒÃÇʱ³£»áÅöµ½ÕâÑùµÄ×Ö¶Î(×î³£¼ûµÄ¾ÍÊǵ绰ºÅÂë)£¬¼´Óд¿Êý×ÖµÄ(ÈçûÓдøÇøºÅµÄµç»°ºÅÂë)£¬ÓÖÓÐÊý×ÖºÍÆäËü×Ö·û»ìºÏ (Èç“ÇøºÅ-µç
»°ºÅÂë”)µÄÊý¾Ý£¬ÔÚµ¼ÈëSQLServer¹ý³ÌÖУ¬»á·¢ÏÖҪô´¿Êý×ÖµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³ÉÁËNULL£¬ÒªÃ´¾ÍÊÇÊý×ÖºÍÆäËü×Ö·û»ìºÏµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³É
ÁËNULL¡£
ΪʲôÓÐЩÊÇ´¿Êý×ÖµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³ÉÁËNULL£¬ÓÐЩȴÊÇÊý×ÖºÍÆäËü×Ö·û»ìºÏµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³ÉÁËNULL£¬ÔÀ´ÊÇÔÚ½«ExcelÊý¾Ýµ¼Èë
SQLServer¹ý³ÌÖУ¬SQLServer»á×ö³öÅжϣ¬ÊDzÉÓÃfloatÐÍ»¹ÊÇnvarcharÐÍÀ´½ÓÊÜÊý¾Ý£¬²âÊÔ·¢ÏÖ(ûÓпÆѧÒÀ¾Ý)£¬SQLServer²ÉÓÃÄÄÒ»ÐÍÈ¡¾öÓÚ½«Òªµ¼Èë
µÄÊý¾ÝÖб¾Éí¾ßÓÐÄÄÒ»Ð͵ļǼÊý±ÈÀý¶à£¬Èç10±ÊÊý¾Ý£¬ÓÐ4±ÊûÓдøÇøºÅµÄµç»°ºÅÂ룬6±ÊÊÇ´øÇøºÅµÄµç»°ºÅÂ룬ÄÇôתµ½SQLServer¾Í»áÑ¡Ôñ
nvarcharÐÍ£¬½á¹û¾ÍÊÇ4±ÊûÓдøÇøºÅµÄµç»°ºÅÂëµ¼¹ýÈ¥Ö®ºóÈ«³ÉÁËNULL£¬·´Ö®ÒàÈ»¡£²»¹ÜÔõôÑù£¬ÎÒÃÇ×îÖÕ¶¼Ï£ÍûSQLServerÊDzÉÓÃnvarcharÀ´½ÓÊÜ
Êý¾Ý£¬±ÏÒâÎÒÃÇÒªµ¼ÈëµÄÊý¾ÝÖÐÓÐÊý×ÖºÍÆäËü×Ö·û»ìºÏµÄÊý¾Ý£¬ÓÃfloatÐÍÀ´½ÓÊÜÊDz»¿ÉÄܵģ¬ÕâÑùÖ»ÒªÎÒÃǽâ¾öÁ˽«´¿Êý×ÖµÄÊý¾Ýת»»³É×Ö·ûÐͲ¢ÈÃ
SQLServer½ÓÊܾͿÉÒÔÁË¡£
ÎÒÊ×ÏÈÏëµ½µÄ¾ÍÊǽ«Õâ¸ö×ֶεÄËùÓÐÊý¾ÝÔÚExcelÖÐÉèÖÃΪÎı¾¸ñʽ£¬¸Õ²Å˵Á˱¾À´¾ÍÊÇÏ£Íûµ¼ÈëSQLServerʱ³ÉΪ×Ö·ûÐÍ£¬µ«½á¹ûÁîÈËʧÍû£¬²»Æð
×÷Óá£
×îÖÕÍøÉÏËÑË÷µ½Á˴𰸣º»ìºÏÊý¾ÝÀàÐÍÁеÄÇ¿ÖƽâÎö——IMEX=1
ʹÓà IMEX=1 Ñ¡²ÎÖ®ºó£¬Ö»ÒªÈ¡ÑùÊý¾ÝÀïÊÇ»ìºÏÊý¾ÝÀàÐ͵ÄÁУ¬Ò»ÂÉÇ¿ÖƽâÎöΪ nvarchar/ntext Îı¾¡£µ±È»£¬IMEX=1 ¶Ôµ¥Ò»Êý¾ÝÀàÐÍÁеĽâÎöÊDz»Ó°
ÏìµÄ¡£
SELECT * INTO Table08
from OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="E:\1.xls";Extended properties="Excel 5.0;HDR=Yes;IMEX=1;"')...[Sheet1$]
×¢£º
1.ÕâÌõÓï¾äÊÇÔÚSQLServer²éѯ·ÖÎöÆ÷ÖÐÖ´ÐУ¬²¢ÇÒҪѡÔñºÃÊý¾Ý¿â£¬·ñÔò»á°ÑÒªµ¼ÈëµÄÊý¾ÝÍù±ðµÄÊý¾Ý¿âÖе¼ÁË¡£
2.Table08ÊÇÊý¾Ýµ¼ÈëºóÔÚSQLServerÖеıíÃû£¬ÊôÓÚн¨£¬ËùÒÔÇëÈ·ÈÏÔÚµ¼ÈëÊý¾ÝÇ°Êý¾Ý¿âÖÐûÓиñíÃû£¬·ñÔò»áÌáʾÒÑ´æÔÚͬһ±íÃû¡£
3.Data Source£¬²»ÒªÁ¬ÔÚÒ»Æðд£¬ÖмäÓÐÒ»¿Õ¸ñ¡£
4.E:\1.xls£¬ÎªExcelËùÔڵľø¶Ô·¾¶ºÍÊý¾Ý¿âÃû¡£
5.Excel 5.0£¬¸ù¾Ý²»Í¬µÄExcel°æ±¾
Ïà¹ØÎĵµ£º
DECLARE
TYPE list IS TABLE OF t_stu.project%TYPE INDEX BY BINARY_INTEGER;
project_list list;
v_pro_num NUMBER;
sql_str VARCHAR(255);
t_stu
SID SUBJECT SCORE
1 Êýѧ &n ......
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc pGetInsertSQL (@TableName varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 v ......
Ò» sqlÓï¾äµÄÖ´Ðв½Öè
1£©Óï·¨·ÖÎö£¬·ÖÎöÓï¾äµÄÓï·¨ÊÇ·ñ·ûºÏ¹æ·¶£¬ºâÁ¿Óï¾äÖи÷±í´ïʽµÄÒâÒå¡£
2£© ÓïÒå·ÖÎö£¬¼ì²éÓï¾äÖÐÉæ¼°µÄËùÓÐÊý¾Ý¿â¶ÔÏóÊÇ·ñ´æÔÚ£¬ÇÒÓû§ÓÐÏàÓ¦µÄȨÏÞ¡£
3£©ÊÓͼת»»£¬½«Éæ¼°ÊÓͼµÄ²éѯÓï¾äת»»ÎªÏàÓ¦µÄ¶Ô»ù±í²éѯÓï¾ä¡£
4£©±í´ïʽת»»£¬ ½«¸´Ô SQL ±í´ïʽת»»Îª½Ï¼òµ¥µÄµÈЧÁ¬½Ó±í´ïʽ¡ ......
ÈçºÎÓÃÃüÁîÉú³ÉSQL SERVER½Å±¾
--ÒÔÏÂÉú³ÉÕû¸öÊý¾Ý¿âµÄSQL½Å±¾£¬ÎÒ²âÊÔÁË£¬Ï൱ºÃÓá£
--(scptxfr.exeµÄ·¾¶ÒªÕýÈ·ÒÔÏÂÊÇÎҵķ¾¶)
declare @cMd varchar(1000)
set @cmd = 'master.dbo.xp_cm ......
/*
ʹÓÃÊÂÎñÈÕÖ¾»Ö¸´Êý¾ÝµÄʵÑé
*/
--1.´´½¨²âÊÔÊý¾Ý¿â
CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA,
FILENAME = 'c:\Db_test.mdf' )
LOG ON
( NAME = Db_test_LOG,
FILENAME = 'c:\Db_test.ldf')
GO
--2.¹ÊÕÏ»¹ÔÄ£ÐÍÉèÖÃΪfullÄ£ÐÍ,ĬÈϵļòµ¥Ä£Ðͱ¸·Ý ......