Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Unanswered: help moving comma delimited data

    I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.

    I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: help moving comma delimited data

    use BCP to create the export-flat-file
    and the Bulk Insert command to import it in the other database

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: help moving comma delimited data

    I can give you examples if this is what you need

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not that I'm a fan of it....but DTS?

    I like Karolyn's idea better though....

    Just make sure the server is mapped....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    learned my lessons well...
    dts, bcp, bulk insert, ...
    i'm getting there !

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, I almost exclusivley use bcp....

    Where in France?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by Brett Kaiser
    Yeah, I almost exclusivley use bcp....

    Where in France?
    Thanks Karolyn and Brett. Much appreciated. This may be a really silly question, but what does BCP stand for?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BCP: Bulk Copy Program (Very original, huh)

    DTS: Data Transformation Service

    DDL: Data Definition Language

    DML: Data Manipulation Language

    DCL: Data Control Language

    BOL: Books Online

    M$: Evil Empire (But hey, it pays the bills)

    IBM: (HAL +1)

    OS/2: Whatever happend to that great OS

    NT: Nice Try...but no OS/2

    DB2: Bullet proof RDBMS

    MOO: My Own Opinion...

    Democratic Convention: Loony Bin

    George Bush: Evil Doer seeker (It's embarrassing)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by Brett Kaiser
    BCP: Bulk Copy Program (Very original, huh)

    DTS: Data Transformation Service

    DDL: Data Definition Language

    DML: Data Manipulation Language

    DCL: Data Control Language

    BOL: Books Online

    M$: Evil Empire (But hey, it pays the bills)

    IBM: (HAL +1)

    OS/2: Whatever happend to that great OS

    NT: Nice Try...but no OS/2

    DB2: Bullet proof RDBMS

    MOO: My Own Opinion...

    Democratic Convention: Loony Bin

    George Bush: Evil Doer seeker (It's embarrassing)
    lol thanks Brett my day is now complete

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Example of Bulk Insert

    Bulk Insert Base.Proprio.Table
    From 'Path + File to Import'
    With (FieldTerminator = ',',
    RowTerminator = ',\n',
    CodePage = 'ACP',
    TabLock) ;

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Example for BCP :

    DECLARE @cmd varchar(8000), @sql varchar(8000);

    Select @sql = '"Select ' + '''Folio''' + ' + ' +
    'Left(DTMAJL,19)' + ' + ' +
    '''Date extraction''' + ' + ' +
    '''N° de table''' + ' + ' +
    '''Table''' + ' + ' +
    '''Version''' + ' as Entête, ' +
    ' * ' +
    'From Base..Table ' +
    'Where DTMAJL > (Select DTLOTVAL From Base..V89TBPL0) And DTMAJL is not NULL"';

    SET @cmd = 'bcp ' + @sql
    + ' queryout ' + 'Chemin et nom du fichier à générer'
    + ' -c -t ' + 'bidule carré'
    + ' -r ' + 'bidule carré\n'
    + ' -U ' + 'username' +' -P ' + 'password' +' -S ' + @@servername
    + ' –h ' + '"TABLOCK"';

    EXEC master..xp_cmdshell @cmd;

  12. #12
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by Karolyn
    Example for BCP :

    DECLARE @cmd varchar(8000), @sql varchar(8000);

    Select @sql = '"Select ' + '''Folio''' + ' + ' +
    'Left(DTMAJL,19)' + ' + ' +
    '''Date extraction''' + ' + ' +
    '''N° de table''' + ' + ' +
    '''Table''' + ' + ' +
    '''Version''' + ' as Entête, ' +
    ' * ' +
    'From Base..Table ' +
    'Where DTMAJL > (Select DTLOTVAL From Base..V89TBPL0) And DTMAJL is not NULL"';

    SET @cmd = 'bcp ' + @sql
    + ' queryout ' + 'Chemin et nom du fichier à générer'
    + ' -c -t ' + 'bidule carré'
    + ' -r ' + 'bidule carré\n'
    + ' -U ' + 'username' +' -P ' + 'password' +' -S ' + @@servername
    + ' –h ' + '"TABLOCK"';

    EXEC master..xp_cmdshell @cmd;
    Thanks Karolyn and Brett for all the help. Time to see if I can get this to work

  13. #13
    Join Date
    Jan 2004
    Posts
    13

    Follow up question

    Say I need to have a sql server just listen for updates that are made in a web server. Currently these two are on seperate boxes but I need to have data passed only from the web server to the sql server. Using the load command is there a simple way to have the sql server continually listening for updates while keeping security high and preventing any possible accidents to the sql database.

    Web server takes input, the sql server listens for updates to the web server and when updates take place the sql server imports the data for storage and review at later dates.

    Does that make sense?

    Would setting up a virtual drive as a holding area and then just pointing the sql database be a good option for this?

    Thanks again for all the prior help.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How would SQL Server "see" The Web Changes?

    Where is the Web Data stored? And why is it not stored in SQL Server in the first place? Doesn't the app talk to the database?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by Brett Kaiser
    How would SQL Server "see" The Web Changes?

    Where is the Web Data stored? And why is it not stored in SQL Server in the first place? Doesn't the app talk to the database?
    The web server and database server are linked using a ODBC connection. What happens is that the data when inputed on the web server is being saved to the web server just not being send to the database server. Can the Load Data Infile command be used to import data to the databse server from the web server with an ODBC connection? I am not having much luck finding that information on the net.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •