Requirements: 1. The table structure in the Excel table and database provided by the customer always has some gaps, the generation of IDs, the associations of various fields, etc.
2.
How to generate GUIDs in Excel.
1. Execute the following code in the Excel macro:
Private Declare ptrsafe function CoCreateGuid Lib "OLE32" (id as any) as Long Private Function createguid () as string< C1/>dim ID (0 to +) as Byte Dim Cnt as Long, GUID as String If cocreateguid (ID (0)) = 0 Then for Cnt = 0 to 15< C5/>createguid = CreateGUID + IIf (ID (CNT) <, "0", "") + hex$ (CNT)) Next Cnt createguid = left$ (createguid , 8) + mid$ (CreateGUID, 9, 4) + mid$ (CreateGUID, 4) + mid$ (CreateGUID, +, 4) + right$ (CreateGUID, +) Else Ms Gbox "Error while creating guid!" End If End Function
2. Write some Excel functions to find the corresponding relationship.
=if (ISNA (VLOOKUP ($O 3, Domain dictionary!) A:b,2,false)), "NULL", VLOOKUP ($O 3, Domain dictionary!) A:b,2,false))
3. Copy the data in Excel into the database.
Ideas: 1. Determine whether the columns in the database have duplicates based on specific columns
Select "" +min (ID) + ', ' from table name where column name in (select column name from (select column name from table name where column name in (select Column name from table name Group By column name has COUNT (*) >1)) as B group By column name, column name has count (*) >1) Group By column name, column name
2. Remove the Duplicate
Delete from table name where ID in ()
3. Modify Data Execution UPDATE statement
4. Duplicate table structure
SELECT * into [dbo]. The table to be copied from [dbo]. Original table
5. Delete Data Structures
TRUNCATE TABLE name
SQL Server inserts data into an existing database