Converting .sql file data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting .sql file data

Author

Topic

Lee Rees
Starting Member

1 Post

Posted - 2006-10-29 : 12:21:06

Hi,

I have two postcode databases in .sql ready to dump into my existing database, the first sql file is 300megs and
the second one is 300k, the reason the second is so small is because it misses out the last 3 letters of the post
code, i dont need theese last 3 digits anyway.

Basically i want to convert the second file so that it is compatible for me to import it into my database and use.

the first file looks like this

INSERT INTO `ZIPCodes` VALUES ('AB101GY',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1476,-2.0975);
INSERT INTO `ZIPCodes` VALUES ('AB101NP',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1467,-2.1074);
INSERT INTO `ZIPCodes` VALUES ('AB101RQ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1467,-2.1107);
INSERT INTO `ZIPCodes` VALUES ('AB101TT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1431,-2.114);
INSERT INTO `ZIPCodes` VALUES ('AB101WX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0);
INSERT INTO `ZIPCodes` VALUES ('AB106AA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.135,-2.1156);
INSERT INTO `ZIPCodes` VALUES ('AB106DE',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1377,-2.1173);

And the second file looks like this:

insert into `hwz_postcodes` values ('postc',0,0,0,0),('AB10',392900,804900,57,-2),('AB11',394500,805300,57,-2),('AB12',393300,801100,57,-2),('AB13',385600,801900,57,-2),('AB14',383600,801100,57,-2),('AB15',390000,805300,57,-2),('AB16',390600,807800,57,-2),('AB21',387900,813200,57,-2),('AB22',392800,810700,57,-2),('AB23',394700,813500,57,-2),('AB25',393200,806900,57,-2),('AB30',370900,772900,56,-2),('AB31',368100,798300,57,-2),('AB32',380800,807200,57,-2),('AB33',355200,815100,57,-2),('AB34',350800

Is there any utility out there that can do this for me? What about the co-ordinates systems, they look incompatible,
so the converter would need to be able to convert the co-ordinates aswell. It would also need to add in all those nulls.

once the file is converted i would then import it through the sql command line.

Why am I such an SQL n00b!???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 12:24:52

Replace all "),(" with ") insert into `hwz_postcodes` values (".
Don't forget to [eventually] add a linebreak before each new "INSERT" command.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 13:11:28

You have a 300 MB text file with INSERT statements in it?

Its going to take a VERY long time to import!

A CSV file or somesuch would import more quickly.

Rather than trying to use an Editor to change the Text file it would be better to import the data and then manipulate it from there, I reckon.

I use an impressive text editor, but when I do Find & Replace in it, on a text file of 300 MB, it takes a long time to make the changes - presumably because it has to keep shuffling memory around to remove all the unused space.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 13:13:25

The second file was "only" 300K something...

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-29 : 13:14:40

Since each file has data in different formats and number of columns, I would insert one file into one table, and the second file into another.

Then you can easily write an

INSERT INTO t1 SELECT ... FROM t2

statment that will bring the values from t2 to t1, performing whatever munging is necessary in the select list (like dividing the coordinates by 10000, etc).

That way you don't have to parse the files to bring them into the same format at all (except the replace on the second file that Peter pointed out). when you are done just drop t2.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-29 : 13:24:55

oh, and you would also put a SET NOCOUNT ON at the top of each file. will cut down on the network chatter.

also, use osql.exe or sqlcmd.exe to execute them. QA might have some difficulty opening up a 300mb file! I know SSMS would!

In the future, it would be much better to export data as csv as Kristen suggests so you could use BULK INSERT.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources