How to Insert txt file into SQL table(DB2)

web

Hi can anyone help me with a sample code that i can use to insert a txt file into an SQL database table in DB2.
Below is the file to be inserted:

1,00:22,63,132,73,142
1,00:50,116,155,126,165
1,01:35,206,182,216,192
1,02:01,233,197,243,207
1,05:14,218,233,228,243
1,07:44,244,261,254,271
1,13:26,272,256,282,266
1,18:14,285,228,298,239
1,18:25,356,238,366,248
1,18:57,496,346,506,356
2,01:07,221,116,231,126
2,01:18,232,74,242,84
2,01:50,196,75,206,85
2,02:01,129,123,139,133
2,12:50,113,157,123,167
2,13:20,107,183,117,193
2,15:58,192,214,202,224
2,16:45,207,185,217,195
2,18:14,202,145,212,155
2,20:22,150,130,160,140
3,02:48,303,62,313,72
3,04:25,361,94,371,104
3,06:18,295,219,305,229
3,09:46,356,239,366,249
3,15:26,392,164,402,174
3,17:20,429,182,439,192
3,22:48,446,242,456,252
3,22:14,507,230,517,240
3,23:05,464,297,474,307
3,23:48,423,355,433,365
4,10:02,40,294,50,304
4,12:18,59,320,69,330
4,15:22,169,280,179,290
4,15:58,194,216,204,226
4,18:03,227,227,237,237
4,18:14,285,229,295,239
4,20:33,358,99,368,109
4,20:45,416,127,426,137
4,21:06,578,216,588,226
4,22:04,539,359,549,369
5,03:13,501,42,511,52
5,03:15,426,56,436,66
5,04:25,361,93,371,103
5,06:51,539,189,549,199
5,07:44,506,233,516,243
5,07:48,401,263,411,273
5,09:10,267,204,277,214
5,12:14,310,125,320,135
5,17:28,233,74,243,84
5,18:44,193,74,203,84

Thanks

web

I have this command which can only insert one row at a time:

INSERT INTO TABLE(ID, T_IME, GEOMETRY) 
  VALUES (1, '12:22',DB2GSE.ST_Point('point(63 132,73 142)',1))

How can i modify this code to insert it all the above data at once.
Thanks

Slawek_Guzek

Hi,

Search in SQL Server BooksOnline for

1. BULK INSERT T-SQL command OR
2. bcp utility OR
3. SQL Server Import and Export Wizard OR
4. SQL Server Integration services, Flat File or Excel Source data sources

Regards,
Slawek

DaveT

Hi web,

Why not do a simple dataport?

web

I did look into the books, but i can't seem to do it that is why i posted it here. I am completely new to SQL and DB2 in particular. i could not see where i can creat this Dataport.
CAn you please help me with the command using the data i provided.
Thanks

Slawek_Guzek

Hi,

Well, that explains a lot. I've assumed your question regards to Microsoft SQL Server - after all you've posted to NAV/Navision, and NAV is working only with Microsoft SQL Server - as far as I know. I didn't put too much attention to (DB2) in the subject.

BTW. Dataport it is a NAV internal object which can be easilly used to read the data from plain text files to the database.

As for answer, perhaps it won't be helpful, but the simplest way is to repeat multiple time the same INSERT INTO statement (you can use Excel go generate text file ), and this will work for sure.

MS SQL Server provides BULK INSERT T-SQL command, which allows to insert multile rows of data dierctly from plain text file, something similar should exists on DB2 as well.

Regards,
Slawek.

garak

instead of "Bulk insert" u can use the LOAD command

http://publib.boulder.ibm.com/infocente ... 008305.htm

or you read these manual for more infos http://www-01.ibm.com/software/data/db2/library/

If you whish to import the flat file in your DB2 table from Navision you can use ADO (search for it in the forum and use the needed connection strings http://www.connectionstrings.com/?carrier=ibmdb2).

Regards

web

Thanks. I manage to insert the whole row one by one using the insert command.
My problem now is to query where two points intersect. Here is my command:

SELECT a.ID, b.ID, CAST(DB2GSE.ST_AsText(DB2GSE.ST_Intersection(a.geometry, b.geometry)) 
		 as VARCHAR(150)) Intersection
FROM ADB5681.MLTABLE  a, ADB5681.MLTABLE  b

But i am having the following error:

DB2 Database Error: ERROR [38SSR] [IBM][DB2/NT] SQL0443N Routine "DB2GSE.ST_INTERSECTION" (specific name "ST_INTERS") has returned an error SQLSTATE with diagnostic text "GSE3427N Incompatible SRSs.". SQLSTATE=38SSR

The problem is a compatibilty issue of :DB2GSE.ST_INTERSECTION
Can anyone help please
Thanks

bbrown

Have you thought about posting this question on a DB2 forum? :idea:

web

I did post it on DB2 forum but no reply yet. Anyway i got the command working
Thanks

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]