导出 MSSQL Server 的数据表为 INSERT语句。

文件:	sqldump.zip
大小:	42KB
下载:	下载

#include "stdafx.h"
#include <assert.h>
#include <string>
#include <vector>

#define for if ( 1 ) for

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" rename("EOF", "EndOfFile")

#define USERNAME "sa"
#define PWD "123456"
#define CNNSTR "Server=192.168.0.254,1433;DATABASE=master;Provider=SQLOLEDB.1"

int VariantToText(_variant_t & variant, _bstr_t & text)
{
    char buf[8192];
    SYSTEMTIME dateTime;
    char datebuf[1024], timebuf[1024];
    unsigned short dosDate, dosTime;

    switch (variant.vt) {
        case VT_NULL:
            strcpy(buf, "NULL");
            break;

        case VT_BSTR:
            sprintf(buf, "'%s'", (const char*)(text = variant));
            break;

        case VT_DATE:
            VariantTimeToSystemTime(variant.date, &dateTime);
            GetDateFormat(LOCALE_SYSTEM_DEFAULT, 0, &dateTime, "yyyy-MM-dd", datebuf, sizeof datebuf);
            GetTimeFormat(LOCALE_SYSTEM_DEFAULT, 0, &dateTime, "HH:mm:ss", timebuf, sizeof timebuf);
            sprintf(buf, "'%s %s'", datebuf, timebuf);
            break;

        default:
            variant.ChangeType(VT_BSTR);
            sprintf(buf, "%s", (const char*)(text = variant));
            break;
    }
    text = buf;
    return 0;
}

int DumpTableRecords(ADODB::_ConnectionPtr & dbase, const char *table)
{
    char buf[8192];

    ADODB::_RecordsetPtr RecPtr;
    _variant_t vtValue, vtName, vtAffected;

    sprintf(buf, "SELECT * FROM %s ; ", table);
    RecPtr = dbase->Execute(buf, &vtAffected, 1);

    while ( !RecPtr->EndOfFile ) {
        _bstr_t text;
        long count = RecPtr->Fields->GetCount();
        printf("INSERT INTO %s(", table);
        for (long i = 0; i < count; i++) {
            text = RecPtr->Fields->GetItem(i)->Name;
            printf("%s%s", i > 0? ", ": "", (const char*)text);
        }
        printf(") VALUES(");

        for (long i = 0; i < count; i++) {
            vtValue = RecPtr->Fields->GetItem(i)->Value;
            VariantToText(vtValue, text);
            printf("%s%s", i > 0? ", ": "", (const char*)text);
        }
        printf("); \n");
        RecPtr->MoveNext();
    }

    return 0;
}

int GetTablesName(ADODB::_ConnectionPtr & dbase, std::vector<std::string> & list)
{
    ADODB::_RecordsetPtr RecPtr;
    _variant_t vtName, vtAffected;

    RecPtr = dbase->Execute("SELECT name FROM sys.tables", &vtAffected, 1);
    while ( !RecPtr->EndOfFile ) {
        _bstr_t text;
        vtName = RecPtr->Fields->GetItem(0L)->Value;
        list.push_back((const char*)(text = vtName));
        RecPtr->MoveNext();
    }

    return 0;
}

int main(int argc, char* argv[])
{
    const char *CnnStr, *UserName, *Pwd;

    if (argc < 3) {
        fprintf(stderr, "usage: %s \n", argv[0]);
        fprintf(stderr, "example: %s %s %s %s\n", argv[0], CNNSTR, USERNAME, PWD);
        return 0;
    }

    std::vector<std::string> tables;
    CoInitialize(0);
    CnnStr = argv[1];
    UserName = argv[2];
    Pwd = argv[3];

    ADODB::_ConnectionPtr g_CnnPtr;
    HRESULT hr = g_CnnPtr.CreateInstance(__uuidof( ADODB::Connection ) );
    assert ( SUCCEEDED(hr) );
    hr = g_CnnPtr->Open(CnnStr, UserName, Pwd, NULL);
    assert ( SUCCEEDED(hr) );

    GetTablesName(g_CnnPtr, tables);
    for (int i = 0; i < tables.size(); i++)
        DumpTableRecords(g_CnnPtr, tables[i].c_str());
    CoUninitialize();

    return 0;
}