Truncate Before Import

[Ben Wilson]

I'm using “Import External Data” to import from an ODBC source.

Is there a way to TRUNCATE a table before import? I checked under the transform “Advanced” options and maybe I'm just not seeing it (or looking in the right place). I would think it would be in the “Table Creation” or “Copy Data” section.

[peterlaursen]

No option to TRUNCATE .. but to DROP TABLE before import.

If you want to TRUNCATE (if for some reason DROP+(RE)CREATE TABLE has some issue) you may create a batch job of 2 SJA jobs

1) a notifications service job executing TRUNCATE TABLE.

2) a import job importing to the now empty table

FAQ links:

http://www.webyog.com/faq/content/27/114/en/introduction-to-the-_sqlyog-job-agent_-sja.html

http://www.webyog.com/faq/content/27/35/en/can-i-execute-more-sja-jobs-at-the-same-time.html?highlight=character%20encoding%20problem

[Ben Wilson]

Thanks Peter.

Using the auto generated table, DROP + (re)CREATE won't work in my case. The (MSSQL) table I'm exporting from has differences in type, indexes and a custom function as default for one of the columns.

This brings me to my next question. I'm guessing it will, but under transform “Map” options, will SQLyog save the “Edit SQL” custom table creation data? Will a custom CREATE have an impact on import field mapping?

[peterlaursen]

If I understand the question, the answer is that everything you have defined in the wizard will be saved in the job file if you choose to save a job file (but not if you choose 'execute now' without saving).

So I do not see why you cannot DROP + (RE)CREATE the table(s). If you execute from the wizard then use the option to 'open existing jobfile'.

[peterlaursen]

Added to our issue tracker here: http://code.google.com/p/sqlyog/issues/detail?id=1598

The request makes good sense when you are importing to an existing table with data and don't have a jobfile with details stored with column mappings for the job.

[Author]

Posts