SQL Server import question

Mark McCumber 331 Reputation points
2022-08-11T19:48:28.217+00:00

I am downloading raw data from the Federal Election Committee’s wed site. However, their data tables are configured like flat files. I am making my relational database in normalized form.

This is my first attempt in importing data and it being automatically inserted into the proper tables.
Can anyone give me the proper guidance?
Thank you,

Accepted answer
  1. Erland Sommarskog 72,421 Reputation points MVP
    2022-08-12T21:08:19.193+00:00

    None of this is a one to one match. If I understand this correctly I will have to create a table copying the FEC’s Candidate table schema. Create some digital voodoo to parse the data from the FEC table into my Candidates table and also have it look up the Candidate’s party affiliation and replace the three-character identification with the proper PartyID number. Along with the Cand_Office_St_ID and Cand_St_ID. These will be foreign keys related to the tblStates StateID column.

    Yes, sounds to me like a reasonable approach. Possibly, you could do the transformation acrobatics in SSIS, but it is a very common technique to read data into staging table, and then perform refinement in SQL. This is sometimes referred to as ELT, Extract, Load, Transform, as opposed to ETL, which is the more common term.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 72,421 Reputation points MVP
    2022-08-11T21:22:33.917+00:00

    There are tons of options, I'm tempted to say too many.

    You can write an SSIS package for the task, but if this is your first time, you may not be acquainted with SQL Server Integration Services. Well, I don't know SSIS myself.

    In SSMS, you can right click a database and select Tasks, and this menu features both Import Data and Import Flat File. Both these are wizards that can guide you step by step.

    There is also the BULK INSERT statement and the command-line tool BCP. They are good if you want to automate things, but you don't want to involve SSIS. I have an article on my web site on how to use these tools: https://www.sommarskog.se/bulkload.html.

    And this is not enough, you can write your own custom program that reads the files and send the data to SQL Server.

  2. Vinodh247 2,191 Reputation points
    2022-08-12T14:40:30.28+00:00

    Hi Mark,

    Thanks for reaching out to Microsoft Q&A.

    You can convert the file int csv format (use '|' delimiter) and then use the Export/Import feature available from the SSMS to import the data into the table. Make sure you have a table created with proper data types inside the database before you attempt to import the csv data. I downloaded a file from the link you have provided and tried to import, dont think there will be any challenges.

    Please Upvote and Accept as answer if the reply was helpful.

    230745-image.png