Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to bulk copy/ upload into Sybase IQ

4 posts in General Discussion Last posting was on 2011-09-17 06:05:32.0Z
Gerry Posted on 2011-09-15 14:21:11.0Z
Sender: 6494.4e72084d.1804289383@sybase.com
From: Gerry
Newsgroups: sybase.public.iq
Subject: How to bulk copy/ upload into Sybase IQ
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e7209d7.64ce.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Sep 2011 07:21:11 -0700
X-Trace: forums-1-dub 1316096471 10.22.241.41 (15 Sep 2011 07:21:11 -0700)
X-Original-Trace: 15 Sep 2011 07:21:11 -0700, 10.22.241.41
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4404
Article PK: 246544

Hi all-

I have a large Excel file from columns A - AA and have 60.9K
rows. Each day I have to load this file into a staging
table in Access 2007 for some local calculation and then I
have to load this table into Sybase through an INSERT INTO
statement. The bad part is, it is taking 1+ hour to load
this table into Sybase.

I was not able to find any article on the web regarding a
better more efficient and faster way to accomplish this
task. Can you share some thoughts, sample code or links to
some articles?

Thank you

Gerry


queryplande Posted on 2011-09-16 09:04:50.0Z
Sender: 1800.4e730f82.1804289383@sybase.com
From: queryplande
Newsgroups: sybase.public.iq
Subject: Re: How to bulk copy/ upload into Sybase IQ
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e731132.183b.1681692777@sybase.com>
References: <4e7209d7.64ce.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Sep 2011 02:04:50 -0700
X-Trace: forums-1-dub 1316163890 10.22.241.41 (16 Sep 2011 02:04:50 -0700)
X-Original-Trace: 16 Sep 2011 02:04:50 -0700, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4405
Article PK: 246545

Hello Gerry,

I expect you do 60.9K INSERTS, right?

So you have 60.9 single row inserts.

Create a statement like ..

INSERT INTO destination_table (field1, field2)
SELECT 'excelrow1.excelfield1', 'excelrow1.excelfield2'
UNION ALL
SELECT 'excelrow2.excelfield1', 'excelrow2.excelfield2'
UNION ALL
...
UNION ALL
SELECT 'excelrow1000.excelfield1',
'excelrow1000.excelfield2'
maybe more...

This should be nearly 1000 times faster.

Regards,

> Hi all-
>
> I have a large Excel file from columns A - AA and have
> 60.9K rows. Each day I have to load this file into a
> staging table in Access 2007 for some local calculation
> and then I have to load this table into Sybase through an
> INSERT INTO statement. The bad part is, it is taking 1+
> hour to load this table into Sybase.
>
> I was not able to find any article on the web regarding a
> better more efficient and faster way to accomplish this
> task. Can you share some thoughts, sample code or links
> to some articles?
>
> Thank you
>
> Gerry


Gerry Posted on 2011-09-16 14:30:17.0Z
Sender: 6494.4e72084d.1804289383@sybase.com
From: Gerry
Newsgroups: sybase.public.iq
Subject: Re: How to bulk copy/ upload into Sybase IQ
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e735d79.273c.1681692777@sybase.com>
References: <4e731132.183b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Sep 2011 07:30:17 -0700
X-Trace: forums-1-dub 1316183417 10.22.241.41 (16 Sep 2011 07:30:17 -0700)
X-Original-Trace: 16 Sep 2011 07:30:17 -0700, 10.22.241.41
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4406
Article PK: 246546

Hi queryplande,

Two questions. I assume, this is a query from Access.
1. But what is the excelrow1 in 'excelrow1.excelfield1'?
How do you specify the row in the query?
2. Since I have some 609,000 rows, you are not suggesting I
type out all 609000 rows in the query? That's just an
approximate number of rows. Some days it can be more, some
days it can be less though. Can you clarify a bit?

INSERT INTO destination_table (field1, field2)
SELECT 'excelrow1.excelfield1', 'excelrow1.excelfield2'
UNION ALL
SELECT 'excelrow2.excelfield1', 'excelrow2.excelfield2'
UNION ALL
...
UNION ALL
SELECT 'excelrow1000.excelfield1',
'excelrow1000.excelfield2'


Thank you,

Gerry

> Hello Gerry,
>
> I expect you do 60.9K INSERTS, right?
>
> So you have 60.9 single row inserts.
>
> Create a statement like ..
>
> INSERT INTO destination_table (field1, field2)
> SELECT 'excelrow1.excelfield1', 'excelrow1.excelfield2'
> UNION ALL
> SELECT 'excelrow2.excelfield1', 'excelrow2.excelfield2'
> UNION ALL
> ...
> UNION ALL
> SELECT 'excelrow1000.excelfield1',
> 'excelrow1000.excelfield2'
> maybe more...
>
> This should be nearly 1000 times faster.
>
> Regards,
>
>
>
>
> > Hi all-
> >
> > I have a large Excel file from columns A - AA and have
> > 60.9K rows. Each day I have to load this file into a
> > staging table in Access 2007 for some local calculation
> > and then I have to load this table into Sybase through
> > an INSERT INTO statement. The bad part is, it is taking
> > 1+ hour to load this table into Sybase.
> >
> > I was not able to find any article on the web regarding
> > a better more efficient and faster way to accomplish
> > this task. Can you share some thoughts, sample code or
> > links to some articles?
> >
> > Thank you
> >
> > Gerry


Leonid Gvirtz Posted on 2011-09-17 06:05:32.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
Newsgroups: sybase.public.iq
Subject: Re: How to bulk copy/ upload into Sybase IQ
References: <4e7209d7.64ce.1681692777@sybase.com>
In-Reply-To: <4e7209d7.64ce.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e7438ac$1@forums-1-dub>
Date: 16 Sep 2011 23:05:32 -0700
X-Trace: forums-1-dub 1316239532 10.22.241.152 (16 Sep 2011 23:05:32 -0700)
X-Original-Trace: 16 Sep 2011 23:05:32 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4407
Article PK: 246547

Hi

Sybase IQ is not designed for row-by-row operations. I think a better approach is to export your Access table into a csv
file and then load into IQ with LOAD TABLE command. See for more details:
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1520/html/iqrefso/X315750.htm.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On 9/15/2011 5:21 PM, Gerry wrote:
> Hi all-
>
> I have a large Excel file from columns A - AA and have 60.9K
> rows. Each day I have to load this file into a staging
> table in Access 2007 for some local calculation and then I
> have to load this table into Sybase through an INSERT INTO
> statement. The bad part is, it is taking 1+ hour to load
> this table into Sybase.
>
> I was not able to find any article on the web regarding a
> better more efficient and faster way to accomplish this
> task. Can you share some thoughts, sample code or links to
> some articles?
>
> Thank you
>
> Gerry