sql table truncate command

[wsu_wright]

In order to preserve table structure and indexes previously defined, we must use the TRUNCATE table command rather than a DROP/REPLACE.  For our larger files this is creating a problem in the time of processing data uploads.

For example, when performing a SAVE TRANSLATE insertion of 8.4 million rows directly into the database via ODBC we were only processing at a rate about 900 rows/sec, a process that would take hours given this dataset size. We changed the process to first run SAVE TRANSLATE to write a CSV data file to the database server and then ran another SAVE TRANSLATE using ODBC to have SQL Server perform a bulk insert using the data file just written. With those changes, the entire process runs in about three or four minutes. We'd like to avoid the 2 step process, especially the writing out of csv data.  We wondered if other users have similar problems with uploading larger data files when trying to preserve previously defined table structures.

Listed below are the save translate syntaxes we are using.

Thanks in advance, David.

*Save the data as a csv file for bulk insert into the database. 'X:' is a mapped network drive and directory.
SAVE TRANSLATE OUTFILE='X:BIPMS_REGSTU_archive.csv'
  /TYPE=CSV
  /REPLACE
  /KEEP=SPBPERS_PIDM, SPRIDEN_ID, STVTERM_CODE, C_P_INDICATOR,
    C_P_DATE, ENROLLED_HOURS,
    SGBSTDN_RESD_CODE, STVCLAS_CODE, SORLFOS_MAJR_CODE_1,
    CLASS_OTHER, SA_IND, table_load_date.

*Truncate the BIS_REGSTU_archive table & replace the data.
SAVE TRANSLATE
  /TYPE=ODBC /CONNECT='DSN=REGSTU_SS10_driver;Description=BI_student;UID=user;PWD=password;APP='+  'PASW Statistics;WSID=wsid;DATABASE=BI_Student'
  /ENCRYPTED
  /SQL='TRUNCATE TABLE [dbo].[BIS_REGSTU_Archive]'
  /SQL='BULK INSERT [dbo].[BIS_REGSTU_Archive] FROM
    ''G:dataBIPMS_REGSTU_archive.csv''' +
    ' WITH (formatfile = ''G:dataRegstu_archive.Fmt'')'
     /KEEP=SPBPERS_PIDM, SPRIDEN_ID, STVTERM_CODE, C_P_INDICATOR,
    C_P_DATE, ENROLLED_HOURS,
    SGBSTDN_RESD_CODE, STVCLAS_CODE, SORLFOS_MAJR_CODE_1,
    CLASS_OTHER, SA_IND, table_load_date
  /MISSING=IGNORE.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Gad Chen]

I suggest first output all data to a temp table so you can use Drop/Replace, e.g My_Temp

Then in SQL Server, just use Query to do others:

TRUNCATE TABLE [dbo].[BIS_REGSTU_Archive]
Insert into [dbo].[BIS_REGSTU_Archive] select * from My_Temp.

Gad

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Wright
Sent: Wednesday, July 22, 2009 3:47 AM
To: [hidden email]
Subject: sql table truncate command

In order to preserve table structure and indexes previously defined, we must use the TRUNCATE table command rather than a DROP/REPLACE.  For our larger files this is creating a problem in the time of processing data uploads.

For example, when performing a SAVE TRANSLATE insertion of 8.4 million rows directly into the database via ODBC we were only processing at a rate about 900 rows/sec, a process that would take hours given this dataset size. We changed the process to first run SAVE TRANSLATE to write a CSV data file to the database server and then ran another SAVE TRANSLATE using ODBC to have SQL Server perform a bulk insert using the data file just written. With those changes, the entire process runs in about three or four minutes. We'd like to avoid the 2 step process, especially the writing out of csv data.  We wondered if other users have similar problems with uploading larger data files when trying to preserve previously defined table structures.

Listed below are the save translate syntaxes we are using.

Thanks in advance, David.

*Save the data as a csv file for bulk insert into the database. 'X:' is a mapped network drive and directory.
SAVE TRANSLATE OUTFILE='X:BIPMS_REGSTU_archive.csv'
  /TYPE=CSV
  /REPLACE
  /KEEP=SPBPERS_PIDM, SPRIDEN_ID, STVTERM_CODE, C_P_INDICATOR,
    C_P_DATE, ENROLLED_HOURS,
    SGBSTDN_RESD_CODE, STVCLAS_CODE, SORLFOS_MAJR_CODE_1,
    CLASS_OTHER, SA_IND, table_load_date.

*Truncate the BIS_REGSTU_archive table & replace the data.
SAVE TRANSLATE
  /TYPE=ODBC /CONNECT='DSN=REGSTU_SS10_driver;Description=BI_student;UID=user;PWD=password;APP='+  'PASW Statistics;WSID=wsid;DATABASE=BI_Student'
  /ENCRYPTED
  /SQL='TRUNCATE TABLE [dbo].[BIS_REGSTU_Archive]'
  /SQL='BULK INSERT [dbo].[BIS_REGSTU_Archive] FROM
    ''G:dataBIPMS_REGSTU_archive.csv''' +
    ' WITH (formatfile = ''G:dataRegstu_archive.Fmt'')'
     /KEEP=SPBPERS_PIDM, SPRIDEN_ID, STVTERM_CODE, C_P_INDICATOR,
    C_P_DATE, ENROLLED_HOURS,
    SGBSTDN_RESD_CODE, STVCLAS_CODE, SORLFOS_MAJR_CODE_1,
    CLASS_OTHER, SA_IND, table_load_date
  /MISSING=IGNORE.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD