SQL Server Performance

Create Table + Bulk Insert

Discussion in 'ALL SQL SERVER QUESTIONS' started by andreandre, Dec 16, 2012.

  1. andreandre New Member

    Hi guys,

    I have a .csv file wich has the following information (exactly like this):

    List Name: User
    Format Version:1.2.5.0
    Date:12/11/2012 12:00:34
    Equipment.:L6867000214
    IP:172.21.12.62

    User,Name,Total,B&W
    [1600],[technical],76,76
    [6611],[Daise],559,559
    [900301],[Richard],0,0

    I want to execute a create table and a bulk insert transaction but I don't know how to insert the information of the top lines of the .csv file like "Equipment.:L6867000214" and "IP:172.21.12.62". I need to create a collumn called "Equipment",other collumn called "IP" and insert this information (repeat "L6867000214" and "172.21.12.62" in all records bulk inserted). The result must be like this:

    IP,Equipment.,User,Name,Total,B&W, Color
    172.21.12.62,L6867000214,[1600],[technical],76,76
    172.21.12.62,L6867000214,[6611],[Daise],559,559
    172.21.12.62,L6867000214,[900301],[Richard],0,0

    I have no idea how to do that. Could anyone here help me out on this issue?

    Thanks a lot!
    andreandre, Dec 16, 2012
    #1

  2. Shehap Moderator

    You can try multiple of options such as :

    1-Either you create a simple DTS job to import the data entity of your excel sheet to your table then you can update the 2 additional columns separately using the DML statement below:

    Update Table set IP = '172.21.12.62',User='L686700021'

    2-Or to Insert the needed data to your table once a time using OPENROWSET Query as below

    declare @v_path nvarchar(1000), @vc_filename nvarchar(1000), @v_sql nvarchar(1000)
    set @v_path = 'C:\Directory'
    set @vc_filename = 'Test.xlsx'

    SET @v_sql = ' insert into table
    SELECT * , '172.21.12.62', 'L686700021' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
    ''Excel 12.0;Database=' + @v_path + '\' + @vc_filename + ';HDR=No;IMEX=1'',
    ''SELECT * FROM [test$]'')'
    print @v_sql
    EXEC sp_executesql@v_sql

    Also you create a specific SSIS Packageto do that job based fundamentally on Data flow Task

    Please let me know if any further help is needed
    Shehap, Jan 4, 2013
    #2

  3. andreandre New Member

    Hi Shehap,

    Thank you for you response.

    Actually, I have several files within this folder wich I want to read the header and create a collumn to them, after that, I want to insert all the data inside the "regular" collumns. The results for the example above should be like this:

    IP, Equipment., User, Name, Total, B&W, Color
    172.21.12.62, L6867000214, [1600], [technical], 76, 76 (NULL)
    172.21.12.62, L6867000214, [6611], [Daise], 559, 559 (NULL)
    172.21.12.62, L6867000214, [900301], [Richard], 0, 0 (NULL)

    This is for each file inserted wich has different IPs, Equipments etc.
    So I need to do a BULK INSERT that reads the files names, avoid copy them twice validating the their path, parse the header information into pivots and save them in a final table.

    Have you ever tried something like this?

    Thanks!
    andreandre, Jan 5, 2013
    #3

Share This Page