gsi

> 테이블 합치는 방법 : 테이블 + 2009 + 09
> 테이블이 존재 하지 않으면 테이블을 생성한다.

---------------------------------------------------------------------------------------
DECLARE @curDateString varchar(100)
DECLARE @tableCount int
DECLARE @sql nvarchar(4000)

-- 현재 시간에서 년, 월을 가져온다.
SET @curDateString = 'TBL_SERVICE_LOG_'
     + convert(varchar(30), year(GetDate()))
     + REPLICATE('0', 2 - len(month(GetDate()))) + convert(varchar(30), month(GetDate()))
--print @curDateString

-- 테이블 존재 하는지 개수를 가져온다.
select @tableCount = count(*)
from sysobjects
where xtype='U' and
name = @curDateString

--print @tableCount

-- 테이블이 존재 하지 않으면 테이블을 생성한다.
IF @tableCount != 1
BEGIN
 SET @sql = N'CREATE TABLE ' + @curDateString + N' (' +
 N'[OrderNo] [int] NULL,' + 
 ...................생략...............
 N'[OriginTelNo] [varchar](50) NULL' +
 N');'
 EXEC(@sql)
END
--ELSE
--BEGIN
 --print '테이블 존재함'
--END

#include "ADODB.h"

class CADODB
{
private:
    _RecordsetPtr  m_RS;
    _CommandPtr    m_CMD;
    _ConnectionPtr m_CONN;

public:
             CADODB();
    virtual ~CADODB();

    CString  db_Error_Message;

    BOOL     DBConnect(char* pWD, char* pID, char* pDataBase, char* pConnectIP);
    BOOL     IsOpen_DB();
    BOOL     IsOpen_RS();
    void     Close_RS();

    BOOL     IsEOF();
    BOOL     Next();
    BOOL     First();

    int      GetRecordCount();
    int      GetFieldCount();

    void     GetRS(_variant_t x, _bstr_t& ret);
    void     GetRS(_variant_t x, _variant_t& ret);
    void     GetRS(_variant_t x, float& ret);
    void     GetRS(_variant_t x, long& ret);
    void     GetRS(_variant_t x, double& ret);

    void     Open_Command(char* StoredProcedureString);
    void     Add_Parameters(_bstr_t _ParameterName,
                             DataTypeEnum _SqlDbType,
                             int _Size,
                             ParameterDirectionEnum _Direction,
                            _variant_t _User_Value);

    BOOL     RowReader();
    long     QueryExecute(_bstr_t _ParameterName);
};



//--------------------------------------------------------------
[ClassView] - [CADODB]

CADODB::CADODB()
{
    m_RS = NULL;
    m_CMD = NULL;
    m_CONN = NULL;
}

CADODB::~CADODB()
{
    if(m_RS != NULL)
    {
        if(IsOpen_RS())
        {
            m_RS->Close();
        }
    }

    if(m_CONN != NULL)
    {
        if(IsOpen_DB())
        {
            m_CONN->Close();
        }
    }
}

BOOL CADODB::DBConnect(char* pWD, char* pID, char* pDataBase, char* pConnectIP)
{
    CString strConnectionString;
    strConnectionString.Format(_T("Provider=SQLOLEDB.1;Persist Security Info=True;
                                   Password = %s;
                                   User ID = %s;
                                   Initial Catalog = %s;
                                   Data Source = %s"),
                                   pWD,
                                   pID,
                                   pDataBase,
                                   pConnectIP);

    m_CONN.CreateInstance("ADODB.Connection");
    m_CONN->ConnectionString = _bstr_t(strConnectionString);
    HRESULT hr = m_CONN->Open("", "", "", -1);

    if(SUCCEEDED(hr))
    {
        return TRUE;
    }
    else
    {
        return FALSE;
    }
}

BOOL CADODB::IsOpen_DB()
{
    return ((m_CONN->GetState() == adStateOpen ) ? TRUE : FALSE);
}

BOOL CADODB::IsOpen_RS()
{
    return ((m_RS->GetState() == adStateOpen ) ? TRUE : FALSE);
}

void CADODB::Close_RS()
{
    if(IsOpen_RS())
    {
        m_RS->Close();
    }
}

BOOL CADODB::IsEOF()
{
    return m_RS->adoEOF;
}

BOOL CADODB::Next()
{
    return (FAILED(m_RS->MoveNext()) ? FALSE : TRUE);
}

BOOL CADODB::First()
{
    return (FAILED(m_RS->MoveFirst()) ? FALSE : TRUE);
}

int CADODB::GetRecordCount()
{
    HRESULT hr;
    ASSERT(m_RS != NULL);
    try
    {
        int count = (int)m_RS->GetRecordCount();

        if (count < 0)
        {
            hr = m_RS->MoveFirst();
            count = 0;
            while (!m_RS->adoEOF)
            {
                count++;
                m_RS->MoveNext();
            }
        }

        if (m_RS->adoEOF)
        {
            m_RS->MoveFirst();
        }

        return count;
    }
    catch (_com_error ex)
    {
        TRACE(_T("Warning: GetRecordCount ErrorMessage: %s;
                  File: %s;
                  Line: %d\n"),
                  ex.ErrorMessage(),
                __FILE__,
                __LINE__);
        return -1;
    }
}

int CADODB::GetFieldCount()
{
    return (int)m_RS->Fields->GetCount();
}

void CADODB::GetRS(_variant_t x, _bstr_t& ret)
{
    ret = m_RS->Fields->Item[x]->Value;
}

void CADODB::GetRS(_variant_t x, _variant_t& ret)
{
    ret = m_RS->Fields->Item[x]->Value;
}

void CADODB::GetRS(_variant_t x, float& ret)
{
    ret = m_RS->Fields->Item[x]->Value;
}

void CADODB::GetRS(_variant_t x, long& ret)
{
    ret = m_RS->Fields->Item[x]->Value;
}

void CADODB::GetRS(_variant_t x, double& ret)
{
    ret = m_RS->Fields->Item[x]->Value;
}

void CADODB::Open_Command(char* StoredProcedureString)
{
    m_CMD.CreateInstance("ADODB.Command");
    m_CMD->CommandText = StoredProcedureString;
    m_CMD->CommandType = adCmdStoredProc;
}

void CADODB::Add_Parameters(_bstr_t _ParameterName,
                               DataTypeEnum _SqlDbType,
                               int _Size,
                               ParameterDirectionEnum _Direction,
                              _variant_t _User_Value)
{
    _ParameterPtr AddParameter;
    AddParameter.CreateInstance("ADODB.Parameter");
    AddParameter->Name = _ParameterName;
    AddParameter->Type = _SqlDbType;
    AddParameter->Size = _Size;
    AddParameter->Direction = _Direction;
    m_CMD->Parameters->Append(AddParameter);
    m_CMD->Parameters->Item[_ParameterName]->Value = _User_Value;
}

BOOL CADODB::RowReader()
{
    try
    {
        m_CMD->ActiveConnection = m_CONN;
        m_CMD->Execute(NULL, NULL, adCmdStoredProc);

        m_RS.CreateInstance(__uuidof(Recordset));
        m_RS->PutRefSource(m_CMD);

        _variant_t vNull;
        vNull.vt = VT_ERROR;
        vNull.scode = DISP_E_PARAMNOTFOUND;
        m_RS->CursorLocation = adUseClient;
        m_RS->Open(vNull, vNull, adOpenStatic, adLockOptimistic, adCmdStoredProc);

        return true;
    }
    catch (_com_error ex)
    {
        if(m_RS != NULL)
        {
            db_Error_Message = ex.ErrorMessage();
            Close_RS();
        }
        else
        {
            db_Error_Message = ex.ErrorMessage();
        }
        return false;
    }
}

long CADODB::QueryExecute(_bstr_t _ParameterName)
{
    m_CMD->ActiveConnection = m_CONN;

    try
    {
        m_CMD->Execute(NULL, NULL, adCmdStoredProc);

        return long(m_CMD->Parameters->Item[_ParameterName]->Value);
    }
    catch (_com_error ex)
    {
        db_Error_Message = ex.ErrorMessage();

        return -1;
    }
}



//--------------------------------------------------------------
[include files] - [stdafx.h]
//------------------------------
#pragma warning(push)
#pragma warning(disable:4146)
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF","adoEOF")
#pragma warning(pop)
//--------------------------------------------------------------


//--------------------------------------------------------------
[ClassView] - [CADOVCApp]

BOOL CADOVCApp::InitInstance()
{
     CoInitialize(NULL); //ADO 초기화
}

int CADOVCApp::ExitInstance()
{
    CoUninitialize();   //ADO 제거

    return CWinApp::ExitInstance();
}


//--------------------------------------------------------------
[include] - [ADOVCDlg.h]


#include "ADODB.h"

class CADOVCDlg : public CDialog
{
public:
    void Execute_Message_Send(CString GetMessage);
    void iMageList_LiST_View();
    BOOL DBConnect();
    CADODB m_ADO;
    CStatusBar  m_wndStatusBar;


//--------------------------------------------------------------
[ClassView] - [CADOVCDlg]

BOOL CADOVCDlg::OnInitDialog()
{
    if(!DBConnect())
    {
        AfxMessageBox("Error DB Connect!!");
    }
}


//--------------------------------------------------------------
[ClassView] - [CADOVCDlg]

void CADOVCDlg::Execute_Message_Send(CString GetMessage)
{
    m_Message_List.AddString(GetMessage);
    m_Message_List.SetTopIndex(m_Message_List.GetCount() - 1);
}

// Stored Procedure - [MS-SQL][Microsoft Visual C++ 6.0]
BOOL CADOVCDlg::DBConnect()
{
    if(m_ADO.DBConnect("550107", "sa", "iMageDB", "SCORPION"))
    {
        return TRUE;
    }
    else
    {
        return FALSE;
    }
}

void CADOVCDlg::OnBtnDelete()
{
    CString msgString;
    long ErrorNUM = 0;
    BOOL b_GetValue = TRUE;

    m_DB_Delete_NUM = GetDlgItemInt(IDC_EDIT_DELETE, &b_GetValue, FALSE);

    msgString.Format(_T("[삭제] - [PF1_1_5_iMageList_DELETE]"));
    m_wndStatusBar.SetPaneText(0, msgString);

    msgString.Format(_T("[삭제]-[PF1_1_5_iMageList_DELETE] - [삭제번호] → [%d]"), m_DB_Delete_NUM);
    Execute_Message_Send(msgString);

    msgString.Format(_T("삭제번호 : [%d]\n\n삭제 하시겠습니까?"), m_DB_Delete_NUM);
    if(AfxMessageBox(msgString, MB_YESNO) == IDYES)
    {
        m_ADO.Open_Command("PF1_1_5_iMageList_DELETE");
        m_ADO.Add_Parameters("@DB_ERR", adInteger, 4, adParamOutput, 0l);
        m_ADO.Add_Parameters("@iMageNUM_1", adInteger, 4, adParamInput, m_DB_Delete_NUM);
        ErrorNUM = m_ADO.QueryExecute("@DB_ERR");

        if(ErrorNUM == 0)
        {
            msgString.Format(_T("[삭제] → [Success]"));
            Execute_Message_Send(msgString);

            iMageList_LiST_View();
        }
        else
        {
            if (ErrorNUM == 544)
            {
                msgString.Format(_T("[iMageList][Delete ERROR] [Delete NUM] : [%d] %s"),
                                 m_DB_Delete_NUM, m_ADO.db_Error_Message);
            }
            else
            {
                msgString.Format(_T("[PF1_1_5_iMageList_DELETE][Delete ERROR] %s"), m_ADO.db_Error_Message);
            }
            Execute_Message_Send(msgString);
        }
    }
}

void CADOVCDlg::OnBtnInsert()
{
    CString msgString;
    long ErrorNUM = 0;
    BOOL b_GetValue = TRUE;

    long Get_INPUT_Error_NUM = 0;
    long Get_iMageNUM = 0;

    GetDlgItemText(IDC_EDIT_PATH, m_DB_Insert_Path);
    GetDlgItemText(IDC_EDIT_FILENAME, m_DB_Insert_FileName);
    m_DB_Insert_FileSize = GetDlgItemInt(IDC_EDIT_FILESIZE, &b_GetValue, FALSE);

    msgString.Format(_T("[저장] - [PF1_1_5_iMageList_iNSERT"));
    m_wndStatusBar.SetPaneText(0, msgString);

    msgString.Format(_T("[저장]-[PF1_1_5_iMageList_iNSERT] - [파일명] → [%s]"), m_DB_Insert_FileName);
    Execute_Message_Send(msgString);

    if(!m_ADO.IsOpen_DB())
    {
        DBConnect();
    }

    m_ADO.Open_Command("PF1_1_5_iMageList_iNSERT");
    m_ADO.Add_Parameters("@DB_ERR", adInteger, 4, adParamOutput, 0l);
    m_ADO.Add_Parameters("@iMageNUM_1", adInteger, 4, adParamOutput, 0l);
    m_ADO.Add_Parameters("@ImagePath_2", adVarChar, 255, adParamInput, _variant_t(m_DB_Insert_Path));
    m_ADO.Add_Parameters("@ImageFile_3", adVarChar, 255, adParamInput, _variant_t(m_DB_Insert_FileName));
    m_ADO.Add_Parameters("@iMageSize_4", adInteger, 4, adParamInput, m_DB_Insert_FileSize);
    ErrorNUM = m_ADO.QueryExecute("@DB_ERR");

    if(ErrorNUM == 0)
    {
        msgString.Format(_T("[추가] → [Success]"));
        Execute_Message_Send(msgString);

        iMageList_LiST_View();
    }
    else
    {
        if (ErrorNUM == 541)
        {
            msgString.Format(_T("[iMageList][iNSERT ERROR] [iMageList FileName] : [%d] %s"),
                             m_DB_Insert_FileName, m_ADO.db_Error_Message);
        }
        else
        {
            msgString.Format(_T("[iMageList][iNSERT ERROR] %s"), m_ADO.db_Error_Message);
        }
        Execute_Message_Send(msgString);
    }
}

void CADOVCDlg::OnBtnSearch()
{
    CString msgString;

    msgString.Format(_T("[조회] - [PF1_1_5_iMageList_LIST]"));
    m_wndStatusBar.SetPaneText(0, msgString);

    iMageList_LiST_View();
}

void CADOVCDlg::iMageList_LiST_View()
{
    CString msgString;
    CString db_Get_String;
    CString str_temp;

    int iROWCOUNT;
    int iField;

    BOOL b_GetValue = TRUE;

    GetDlgItemText(IDC_EDIT_SEARCH_FILENAME, m_DB_Search_FileName);
    m_DB_UserTOP = GetDlgItemInt(IDC_EDIT_USER_TOP, &b_GetValue, FALSE);

    if(m_DB_Search_FileName.GetLength() == 0 || !b_GetValue)
    {
        m_DB_Search_FileName = '%';
    }
    else
    {
        m_DB_Search_FileName = m_DB_Search_FileName + '%';
    }

    msgString.Format(_T("[조회][Stored_Procedure - %s] - [검색문자 : %s]"),
                     _T("PF1_1_5_iMageList_LIST"), m_DB_Search_FileName);
    Execute_Message_Send(msgString);

    if(!m_ADO.IsOpen_DB())
    {
        DBConnect();
    }

    m_ADO.Open_Command("PF1_1_5_iMageList_LIST");
    m_ADO.Add_Parameters("@USER_TOP", adInteger, 4, adParamInput, m_DB_UserTOP);
    m_ADO.Add_Parameters("@iMageName", adVarChar, 255, adParamInput, _variant_t(m_DB_Search_FileName));

    if(m_ADO.RowReader())
    {
        _bstr_t dbFieldGetValue;

        iROWCOUNT = m_ADO.GetRecordCount();
        if(iROWCOUNT <= 0) return;
        iField = m_ADO.GetFieldCount();

        msgString.Format(_T("Record : [%d]"), iROWCOUNT);
        m_wndStatusBar.SetPaneText(1, msgString);
        msgString.Format(_T("Field : [%d]"), iField);
        m_wndStatusBar.SetPaneText(2, msgString);

        m_ADO.GetRS(_variant_t((short)0), dbFieldGetValue);

        m_DB_Search_List.DeleteAllItems();
        for(int iROW = 0; iROW < iROWCOUNT; iROW++)
        {
            str_temp.Format(_T("%d"), (iROW + 1));
            m_DB_Search_List.InsertItem(iROW, str_temp);
            for(int jCOL = 0; jCOL < iField; jCOL++)
            {
                m_ADO.GetRS(_variant_t((short)jCOL), dbFieldGetValue);
                db_Get_String.Format(_T(("%s"), (LPCTSTR)dbFieldGetValue);

                m_DB_Search_List.SetItemText(iROW, (jCOL + 1), db_Get_String);
            }

            m_ADO.Next();
        }
        m_ADO.Close_RS();
    }
    else
    {
        msgString.Format(_T("Table ROW Read Error [%s]"), m_ADO.db_Error_Message);
        Execute_Message_Send(msgString);
    }
}



----------------------------------------------------------------
[MS-SQL] - [Stored Procedure]
----------------------------------------------------------------
[CREATE TABLE] - [iMageList]
--------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[iMageList]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table   [dbo].[iMageList]
GO

CREATE TABLE [dbo].[iMageList]
(
             [iMageNUM] [int] IDENTITY (1, 1) NOT NULL ,
             [iMagePath] [varchar] (255) COLLATE Korean_Wansung_CI_AS NOT NULL ,
             [iMageFile] [varchar] (255) COLLATE Korean_Wansung_CI_AS NOT NULL ,
             [iMageSize] [int] NULL
) ON         [PRIMARY]
GO

ALTER TABLE  [dbo].[iMageList] WITH NOCHECK ADD
CONSTRAINT   [PK_iMageList] PRIMARY KEY  CLUSTERED
(
             [iMageNUM]
) ON         [PRIMARY]
GO

----------------------------------------------------------------


----------------------------------------------------------------
[iMageDB - iMageList] Table
+--------------------------------+
|     [iMageDB] - [DataBase]     |
+--------------------------------+
|            iMageList           |
+----------------+---------+-----+
| iMageNUM       | int     |   4 |
| iMagePath      | varchar | 255 |
| iMageFile      | varchar | 255 |
| iMageSize      | int     |   4 |
+----------------+---------+-----+
----------------------------------------------------------------


----------------------------------------------------------------
[Stored Procedures]

----------------------------------------------------------------
[PF1_1_5_iMageList_LIST]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_LIST]
                (@USER_TOP  [INT],
                 @iMageName [VARCHAR](255))

AS

SET ROWCOUNT @USER_TOP

SELECT
            [dbo].[iMageList].iMagePath,
            [dbo].[iMageList].iMageFile,
            [dbo].[iMageList].iMageSize,
            [dbo].[iMageList].iMageNUM
FROM        [dbo].[iMageList]
WHERE      ([dbo].[iMageList].iMageFile LIKE @iMageName)
ORDER BY    [dbo].[iMageList].iMageNUM DESC

GO


----------------------------------------------------------------
[PF1_1_5_iMageList_DELETE]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_DELETE]
                (@DB_ERR      [INT] OUTPUT,
                 @iMageNUM_1  [INT])

AS

SET @DB_ERR = 0
SET XACT_ABORT OFF

BEGIN TRANSACTION

DELETE [iMageDB].[dbo].[iMageList]
WHERE ([iMageDB].[dbo].[iMageList].[iMageNUM] = @iMageNUM_1)

IF @@ERROR <> 0

   BEGIN
         ROLLBACK TRANSACTION
         SET @DB_ERR = 544
         RETURN
   END

COMMIT TRANSACTION
RETURN

GO


----------------------------------------------------------------
[PF1_1_5_iMageList_iNSERT]
----------------------------------------------------------------
CREATE PROCEDURE [PF1_1_5_iMageList_iNSERT]
                (@DB_ERR           [INT] OUTPUT,
                 @iMageNUM_1       [INT] OUTPUT,
                 @iMagePath_2      [VARCHAR](255),
                 @iMageFile_3      [VARCHAR](255),
                 @iMageSize_4      [INT])

AS

SET @DB_ERR = 0
SET XACT_ABORT OFF

BEGIN TRANSACTION

INSERT INTO [iMageDB].[dbo].[iMageList]
           ([iMagePath],
            [iMageFile],
            [iMageSize])
VALUES
           (@iMagePath_2,
            @iMageFile_3,
            @iMageSize_4)

IF @@ERROR <> 0

   BEGIN
         ROLLBACK TRANSACTION
         SET @DB_ERR = 541

         RETURN
   END

SET @iMageNUM_1 = (SELECT MAX([dbo].[iMageList].iMageNUM) FROM [iMageDB].[dbo].[iMageList])

COMMIT TRANSACTION
RETURN

GO
----------------------------------------------------------------


----------------------------------------------------------------
[T-SQL]-[Data Insert]
----------------------------------------------------------------
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_001.jpg',  34511)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_002.jpg',  83316)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_003.jpg',  95997)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_004.jpg', 192613)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_005.jpg',  33858)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_006.jpg',  67318)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'CostumePlay_007.jpg',  64452)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_001.jpg', 224121)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_002.jpg',  58777)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_003.jpg',  33838)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_004.jpg', 191308)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_005.jpg', 467618)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_006.jpg', 184552)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'imgGirl_007.jpg',  78885)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_001.jpg',  56778)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_002.jpg', 123445)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_003.jpg', 179880)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_004.jpg',  98878)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_005.jpg', 105567)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_006.jpg',  58890)
INSERT INTO iMageList ([iMagePath], [iMageFile], [iMageSize]) VALUES ('iMageList\', 'jpnGirl_007.jpg',  71103)
----------------------------------------------------------------

기본 DB를 만들면 sqlite는 idx등의 고유번호를 자동증가가 안되는거 같다.

그래서 아래와 같이 Execute SQL에서 아래와 같은 쿼리를 통해서
값을 처리 해야 한다.

위와 같이 s1 이란 것과 "sqlite_sequence" 가 생기게 된다.

insert  into aaa.dbo.aa select * from bbb.dbo.bb;
-- 이걸 사용하게 되면 다른 DB의 같은 테이블 구조를 가지고 있을때 내용을 복사할 수 있다.

DROP TABLE #MyTempTable; --임시테이블을 만든다.
-- CTE 구조로 해서 원하는 내용을 뽑아 온다.
with empCTE(
 _idx,
 _uid,
 _puid)
as
(
 -- boss only
 select idx, uid, puid
 from userList_tmp
 where uid = 'test1' and grade = '2' --최초 탐색 시점

 union all

 select aa.idx, aa.uid, aa.puid
 from userList_tmp as aa inner join empCTE as bb
 on aa.puid = bb._uid
 --where bb.level < 3
)
select _idx into #MyTempTable from empCTE --임시테이블로 복사
GO
-- 임시 테이블의 내용을 원본 테이블에서 삭제
delete from userList_tmp where idx in (select * from #MyTempTable)  제

declare @idx int
declare @account varchar(20)
declare @nickname varchar(20)
declare @account2 varchar(20)
declare @nickname2 varchar(20)

set @idx = 0
set @account = 'test'
set @nickname = 'Dummy'

while (1=1)
begin
 if @idx > 9999 break;
 set @account2 = @account + convert(varchar(4), @idx)
 set @nickname2 = @nickname + convert(varchar(4), @idx)
 print @idx
 print @account2 + @nickname2
 INSERT INTO [NDT].[dbo].[USERACCOUNT]
           ([ACCOUNT]
           ,[PASSWORD]
           ,[NICKNAME]
           ,[MONEY]
           ,[LOGINSTATE])
     VALUES
           (@account2
           ,'1'
           ,@nickname2
           ,0
           ,0)
 set @idx = @idx + 1
end