select * from ERP.CRM.INQ

linked_server_name     Linked server referencing the OLE DB data source – 数据库服务器名

catalog             Catalog in the OLE DB data source that contains the object – 数据库

schema             Schema in the catalog that contains the object – 可以省略，类是于 dbo.

object_name         Data object in the schema – 表

bulk insert xsxt.dbo.tabletest from 'c:/data.txt'  

 with(   

   FIELDTERMINATOR=',',   

   ROWTERMINATOR='/n'  

)

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 ],   

)

--创建临时表#Tmp

create table #Tmp  

(

    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1

    WokNo                varchar(50),   

    primary key (ID)      --定义ID为临时表#Tmp的主键      

);

Select * from #Tmp    --查询临时表的数据

truncate table #Tmp  --清空临时表的所有数据和约束

　declare @t1 table(t1 int)

　declare @t2 table(t2 int)

　set @t1=@t2 --错误

CREATE TYPE [dbo].[MyTableType] AS TABLE(

            Id int, 

Code nvarhcar(50)

)

Create PROCEDURE [dbo].[MyProcedure]

(

            @myTableType AS MyTableType  READONLY

)

       DataTable myDataTable = new DataTable("MyTable");

            drProspectFileType.Columns.Add("Id", typeof(int));

            drProspectFileType.Columns.Add("Code", typeof(string));

SqlParameter para = new SqlParameter("@myTableType ", SqlDbType.Structured)

                {

                    TypeName = "dbo. MyTableType",

                    Direction = ParameterDirection.Input,

                    Value = myDataTable

                };

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Text;

namespace DataFixer

{

    internal class Program

    {

        private static void Main(string[] args)

        {

            var crmConnectionString = ConfigurationManager.ConnectionStrings["CRMDbConnectionString"].ConnectionString;

            var erpConnectionString = ConfigurationManager.ConnectionStrings["ERPDbConnectionString"].ConnectionString; 

var UpdateSql = ConfigurationManager. AppSettings["UpdateSql"]; 

var CleanSql = = ConfigurationManager. AppSettings["CleanSql "]; 

            PrintUseage();

            try

            {

                var outPutFolder = ConfigurationManager.AppSettings["OutputFolder"];

                if (string.IsNullOrEmpty(outPutFolder) || !Directory.Exists(outPutFolder) || string.IsNullOrEmpty(crmConnectionString) || string.IsNullOrEmpty(axConnectionString))

                {

                    Console.WriteLine("Wrong configuration, please check by the usage description ....");

                    PrintUseage();

                }

                // get from ContactId & EmailAddress1 from CRM where EamialAddress 1 Is Not Null and ContactId is not Null

                var crmFilePath = Path.Combine(outPutFolder, "crmIdEmail.txt");

                if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["CRMIdAndEmailFromCRMOutputFile"]))

                {

                    Console.WriteLine("Export CRMId, Email from CRM database to " + ConfigurationManager.AppSettings["CRMIdAndEmailFromCRMOutputFile"] + " ... ");

                    var contactIdEmailDataTable = new DataTable();

                    FillDataTable(contactIdEmailDataTable,

                        crmConnectionString,

                        "SELECT [ContactId],[EmailAddress1] AS [Email] FROM dbo.Contact (nolock) WHERE [ContactId] IS NOT NULL AND [EmailAddress1] IS NOT NULL");

                    ExportCrmidEMailFromCrmToCsv(contactIdEmailDataTable, crmFilePath);

                    Console.WriteLine("Export CRMId, Email from CRM database to " + ConfigurationManager.AppSettings["CRMIdAndEmailFromCRMOutputFile"] + "completed.");

                }

                // get TransacitonId,CustomerId from Ax database

                var axFilePath = Path.Combine(outPutFolder, "crmIdAndTransactionId.txt");

                if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["TransactionIdAndCustomerIdFromAxOutputFile"]))

                {

                    Console.WriteLine("Export TransactionId, CustomerId from AX database to " + ConfigurationManager.AppSettings["TransactionIdAndCustomerIdFromAxOutputFile"] + " ... ");

                    var srcOrderIdCusAccountTable = new DataTable();

                    FillDataTable(srcOrderIdCusAccountTable,

                        axConnectionString,

                        @"SELECT [Store]+[Terminal]+[TransactionID] AS TransactionId , [CRMCustAccount]  FROM [RBOTransactionTable] (nolock) WHERE CRMCUSTACCOUNT != '' and Dataareaid='mrs'");

                    ExportTransactionIdAndCustomerIdToCsv(srcOrderIdCusAccountTable, axFilePath);

                    Console.WriteLine("Export TransactionId, CustomerId from AX database to " +

                        ConfigurationManager.AppSettings["TransactionIdAndCustomerIdFromAxOutputFile"] +

                        " completed.");

                }

                Console.WriteLine("Generating update.sql...");

                File.WriteAllText(Path.Combine(outPutFolder, "update.sql"), string.Format(UpdateSql, crmFilePath, axFilePath));

                Console.WriteLine("Generating clean.sql ....");

                File.WriteAllText(Path.Combine(outPutFolder, "clean.sql"), CleanSql);

                Console.WriteLine("Press any key to exit.");

                Console.Read();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Excepiton occured during the process:" + Environment.NewLine

                    + "Exception message:" + ex.Message + Environment.NewLine +

                    "Stack Trace:" + ex.StackTrace);

                Console.WriteLine("You may need prelong the connectiontimeout time in configure file and then rerun this applicaton.");

            }

        }

        private static void PrintUseage()

        {

            Console.WriteLine(new string('*', 40) + "Usage" + new string('*', 40));

            Console.WriteLine();

            Console.Write(@"Ensure you modified config based on you real enviroments: 

    1.ConnectionString: CRMDbConnectionString: database connection string of CRM

    2.ConnectionString:AXDbConnectionString: database of AX

    3.AppSetting: OutputFolder: the folder you want to put all the files.

      And it should can be  accessed by RODS server.

      Otherwise copy all files generated to a folder that can be accessed manually.

      Recommend a UNC path like \\servername\foldername. 

      The files generated like below, any you maybe need modify it before you run it:

        a. crmIdEmail.txt a text file from CRM stores CRMId, Email info

        b. crmIdAndTransactionId.txt a text file from AX stores TransactionId, CustomerId info

        c. update.sql which is to update the RODS Customer and Order table

        d. clean.sql  delete all the temp global table");

            Console.WriteLine();

            Console.WriteLine();

            Console.WriteLine(new string('*', 40) + "Usage" + new string('*', 40));

        }

        private static void ExportTransactionIdAndCustomerIdToCsv(DataTable dt, string filePath)

        {

            var stringBuilder = new StringBuilder(1024 * 1024);

            foreach (DataRow row in dt.Rows)

            {

                stringBuilder.Append(string.Format("{0},{1}", row[0], row[1]));

                stringBuilder.AppendLine();

            }

            File.WriteAllText(filePath, stringBuilder.ToString());

        }

        private static void ExportCrmidEMailFromCrmToCsv(DataTable contactIdEmailDataTable, string filePath)

        {

            var stringBuilder = new StringBuilder(1024 * 1024);

            foreach (DataRow row in contactIdEmailDataTable.Rows)

            {

                stringBuilder.Append(string.Format("{0},{1}", row[0], row[1]));

                stringBuilder.AppendLine();

            }

            File.WriteAllText(filePath, stringBuilder.ToString());

        }

        private static void FillDataTable(DataTable filledDt, string connectionString, string commandText)

        {

            using (var connection = new SqlConnection(connectionString))

            {

                connection.Open();

                var sqlCommand = connection.CreateCommand();

                sqlCommand.CommandText = commandText;

                sqlCommand.CommandType = CommandType.Text;

                var dataAdapter = new SqlDataAdapter

                {

                    SelectCommand = sqlCommand

                };

                dataAdapter.Fill(filledDt);

            }

        }

    }

}

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

/*Update Customer Table*/

CREATE TABLE ##crmIdEmailInMemory([CrmId] nvarchar(50) ,[EMail] nvarchar(150));

BULK INSERT ##crmIdEmailInMemory FROM '\\servername \test\crmIdEmail.txt'

WITH(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

--Execute Update SQL

UPDATE C SET C.CrmId = T.CrmId

FROM dbo.[Customer] AS C

INNER JOIN ##crmIdEmailInMemory AS T

ON C.Email = T.EMail WHERE C.CrmId IS NULL

/*--

Update Order Table

--*/

CREATE TABLE ##tranasactionIdAndCustomerIdInMemory([TransactionId] nvarchar(50) ,[CustomerId] nvarchar(50));

BULK INSERT ##tranasactionIdAndCustomerIdInMemory FROM '\\servername \test\crmIdAndTransactionId.txt' 

WITH(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

);

--Execute Update SQL On Orders table 

-- CustomerId

WITH CustomerTempTable(

TransactionId,

CustomerId 

)

AS

(SELECT Trans.TransactionId AS TransactionId, C.CustomerId AS CustomerId 

FROM dbo.Customer AS C

INNER JOIN ##tranasactionIdAndCustomerIdInMemory AS Trans

ON C.CrmId = Trans.CustomerId)

UPDATE  O

SET O.CustomerId = T.CustomerId

FROM dbo.[Order] AS O

INNER JOIN CustomerTempTable AS T

ON O.SrcOrderId = T.TransactionId

Where O.CustomerId = -1 And O.ChannelId = 1;

GO