locked
Question about moving from CSV --> SQL Server RRS feed

  • Question

  • I'm trying to setup a process to move data from a CSV into my SQL 2008 db.

     

    Currently I use bulk insert to move the data to a single table. I run some commands to cleanup the data then use a stored procedure to move data to the correct tables. My main problem is that I'm unsure how to setup foreign key constraints. Can someone look at my transactions and let me know if I have the right idea? This currently works but I want to see if there is a better solution.

    --the adminperson table contains raw data from a CSV/flat file
     
    --The first transaction inserts new jobnames into the normalized jobname table and
    --assigns these entries a PK. note that adminperson.cat = personjobname.jobnamename
     
    --The 2nd transaction updates the person table and inserts the foreign key jobnameid into it
     
    BEGIN TRANSACTION staging
     
    --use Persondb;
    INSERT INTO dbo.personjobname
                (jobnamename)
    SELECT DISTINCT cat
    FROM   adminperson
    WHERE  cat NOT IN (SELECT personjobname.jobnamename
                       FROM   personjobname)
     
    --rollback transaction
    COMMIT TRANSACTION
     
    BEGIN TRANSACTION bravo
     
    UPDATE person
    SET    person.jobnameid = (SELECT personjobname.jobnameid
                               FROM   adminperson
                                      JOIN personjobname
                                        ON adminperson.cat =
                                           personjobname.jobnamename)
     
    COMMIT TRANSACTION
     


    Thursday, October 20, 2011 3:11 AM

Answers

  • HI Deluxe !

    Here are few approaches you may use depending on your case;

    1) You may create a DTS Package , define the Source(Flat File/Text File) and Destination Connection(s) and identify the csv file you want to transform from Source to Destination (MS SQL 2000)

    2) You may create the SSIS Package and use Transform Table utility to copy csv file data from Source to Destination DB (MS SSIS)

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Note : Always post your table DDL script , sample data and expected output.

    Thanks, Hasham

    • Marked as answer by KJian_ Thursday, October 27, 2011 7:53 AM
    Thursday, October 20, 2011 11:36 AM
    Answerer
  • also Bulkinsert here sample http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by KJian_ Thursday, October 27, 2011 7:53 AM
    Thursday, October 20, 2011 11:58 AM

All replies

  • HI Deluxe !

    Here are few approaches you may use depending on your case;

    1) You may create a DTS Package , define the Source(Flat File/Text File) and Destination Connection(s) and identify the csv file you want to transform from Source to Destination (MS SQL 2000)

    2) You may create the SSIS Package and use Transform Table utility to copy csv file data from Source to Destination DB (MS SSIS)

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Note : Always post your table DDL script , sample data and expected output.

    Thanks, Hasham

    • Marked as answer by KJian_ Thursday, October 27, 2011 7:53 AM
    Thursday, October 20, 2011 11:36 AM
    Answerer
  • also Bulkinsert here sample http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by KJian_ Thursday, October 27, 2011 7:53 AM
    Thursday, October 20, 2011 11:58 AM