分类
学习笔记

MySQL 语句级避免重复插入—— Insert Select Not Exist

想要插入一条数据,要避免重复插入,又不想折腾两回数据库连接操作,可以参考如下办法。

    INSERT INTO table(column1,column2,column3 ...columnN)  
    SELECT value1,value2,value3 ...valueN  
    FROM dual  
    WHERE NOT EXISTS(  
          SELECT *  
          FROM table  
          WHERE value = ?  
    );  

dual是为了构建查询语句而存在的表,Oracle中很常见,配合INSERT … SELECT构建成我们需要的表,并指定了数据项.
EXISTS通过这个判断是否存在的函数,就免去了我们做IF-ELSE的冗繁操作

分类
日记

TXT文件导入sql数据库时分隔符控制

小结:

Txt文本文件分隔符format有多种。例如:逗号,分号,制表符等

详细参考 :http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx

直接用jet引擎导入,例如:

SELECT  *
INTO    jiaofei
FROM    OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
                   'Text;HDR=YES;FMT=TabDelimited;DATABASE=F:\'
, jiaofei#txt)
 --aa#txt是文本文件名aa.txt 

其中:“text;HDR=Yes;FMT=TabDelimited”;

hdr=yes 意思是首行是否包含列名称

FMT=TablDelimited 意思是格式的分割限定

实际导入时,发现源文件是使用”制表符“分隔的,但导入后没有分隔。

经查资料,发现如下两种解决方案:

方案一:修改注册表。

修改注册表中:HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text “format”的键值

默认的分隔符为CSV分隔“,”文件是制表符分隔的,那么就要把”format”的键值改成“Tabdelimited”

方案二:增加Schema.ini来订制格式

数据类型, Schema.ini 必须与txt或者csv文件在同一目录下才能生效。

Schema.ini 内容

[test1.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
a=F1 Char Width 3
b=F2 Char Width 10
c=F3 Integer

分类
学习笔记

SqlBulkCopy使用

SQLBulkCopy用于批量复制数据。性能优势明显。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是,数据源不限于 SQL Server;可以使用任何数据源,只要数据可加载到 DataTable 实例或可使用 IDataReader实例读取数据(如:SqlDataReader)。

示例:

    //sqlbulkCopy 批量插入数据库
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 10000;
                //超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;             
                //设置要批量写入的表
                sqlBC.DestinationTableName = "dbo.tbl_test";

                //自定义的datatable和数据库的字段进行对应
                sqlBC.ColumnMappings.Add("A", "col_A");
                sqlBC.ColumnMappings.Add("B", "col_B");

                //批量写入
                sqlBC.WriteToServer(dt);
            }

 

分类
学习笔记

SQLserver字符串分割函数

来源互联网:

一、按指定符号分割字符串,返回分割后的元素个数

CREATE FUNCTION [dbo].[Get_StrArrayLength]
    (
      @str VARCHAR(1024) ,  --要分割的字符串  
      @split VARCHAR(10)  --分隔符号  
    )
RETURNS INT
AS 
    BEGIN  
        DECLARE @location INT  
        DECLARE @start INT  
        DECLARE @length INT  
  
        SET @str = LTRIM(RTRIM(@str))  
        SET @location = CHARINDEX(@split, @str)  
        SET @length = 1  
        WHILE @location <> 0 
            BEGIN  
                SET @start = @location + 1  
                SET @location = CHARINDEX(@split, @str, @start)  
                SET @length = @length + 1  
            END  
        RETURN @length  
    END  

二、按指定符号分割字符串,返回分割后指定索引的第几个元素

CREATE FUNCTION [dbo].[Get_StrArrayStrOfIndex]
    (
      @str VARCHAR(1024) ,  --要分割的字符串  
      @split VARCHAR(10) ,  --分隔符号  
      @index INT --取第几个元素  
    )
RETURNS VARCHAR(1024)
AS 
    BEGIN  
        DECLARE @location INT  
        DECLARE @start INT  
        DECLARE @next INT  
        DECLARE @seed INT  
  
        SET @str = LTRIM(RTRIM(@str))  
        SET @start = 1  
        SET @next = 1  
        SET @seed = LEN(@split)  
    
        SET @location = CHARINDEX(@split, @str)  
        WHILE @location <> 0
            AND @index > @next 
            BEGIN  
                SET @start = @location + @seed  
                SET @location = CHARINDEX(@split, @str, @start)  
                SET @next = @next + 1  
            END  
        IF @location = 0 
            SELECT  @location = LEN(@str) + 1  
 --这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。  
    
        RETURN SUBSTRING(@str,@start,@[email protected]start)  
    END  
分类
日记

Sql Server2000/2005 查询所有表的大小和空间使用信息

sql Server2000:

SELECT  OBJECT_NAME(id) tablename ,
        8 * reserved / 1024 reserved ,
        RTRIM(8 * dpages / 1024) + 'Mb' used ,
        8 * ( reserved - dpages ) / 1024 unused ,
        8 * dpages / 1024 - rows / 1024 * minlen / 1024 free ,
        rows ,
        *
FROM    sysindexes
--where indid=1
ORDER BY reserved DESC

sql Server2005:

IF NOT EXISTS ( SELECT  *
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[tablespaceinfo]')
                        AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
    CREATE TABLE tablespaceinfo --创建结果存储表
        (
          nameinfo VARCHAR(50) ,
          rowsinfo INT ,
          reserved VARCHAR(20) ,
          datainfo VARCHAR(20) ,
          index_size VARCHAR(20) ,
          unused VARCHAR(20)
        )

DELETE  FROM tablespaceinfo
 --清空数据表

DECLARE @tablename VARCHAR(255)
 --表名称

DECLARE @cmdsql VARCHAR(500)

DECLARE Info_cursor CURSOR FOR
SELECT o.name
FROM dbo.sysobjects o WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
AND o.name NOT LIKE N'#%%' ORDER BY o.name

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        IF EXISTS ( SELECT  *
                    FROM    dbo.sysobjects
                    WHERE   id = OBJECT_ID(@tablename)
                            AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
            EXECUTE sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname',
                N'@tbname varchar(255)', @tbname = @tablename

        FETCH NEXT FROM Info_cursor
INTO @tablename
    END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO

--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'TRUE' 
--itlearner注:显示表信息
SELECT  *
FROM    tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC 

数据库运行一段时间,数据增加,有的表大小都过G 的,根据需要,可以适当删除一些垃圾数据信息,另外清理日志,以及收缩数据库,也是处理数据库的必要手段。给数据瘦身吧!