SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BULK INSERT from Select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

santkris4
Starting Member

10 Posts

Posted - 06/07/2011 :  13:11:28  Show Profile  Reply with Quote
I know we could use Bulk Insert to load data from a data file. But I was hoping to use the same with a "Select" statement instead of "data file". Is this at all possible? I have around 22 Million rows that I will be migrating on a weekly basis using a linked server and thought this will speed up things.

All suggestions are appreciated!

Edited by - santkris4 on 06/07/2011 13:12:15

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 06/07/2011 :  13:49:30  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
is the data file xml? if so you could use SQLBULKXML. amazing speed and can gobble up huge files within minutes. ingesting from data file: csv, xml etc could be faster and more efficient

If you don't have the passion to help people, you have no passion
Go to Top of Page

santkris4
Starting Member

10 Posts

Posted - 06/07/2011 :  14:00:20  Show Profile  Reply with Quote
Thanks yosiasz. I don't have any file. I was hoping to have a "Select" query and use that output to be bulk inserted into another table.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 06/07/2011 :  14:29:23  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
gotcha. plug this in a while loop to batch process it. if you try to do the whole 22 million rows, it could kill your server

insert into destinationtable
SELECT field1,
FROM sourceTable

or you could use an SSIS process to move that data for you on a schedule

If you don't have the passion to help people, you have no passion
Go to Top of Page

santkris4
Starting Member

10 Posts

Posted - 06/07/2011 :  15:01:54  Show Profile  Reply with Quote
Thanks.

I tried the below and it worked very fast.

Insert Bulk Table1
Select col1,col2,.... from Table2.

One issue is that the above is not possible if you have any columns of Bigint data type.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 06/07/2011 :  16:21:56  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
are source and destination table column data type the same? are you moving bigint to bigtint?

If you don't have the passion to help people, you have no passion
Go to Top of Page

santkris4
Starting Member

10 Posts

Posted - 06/10/2011 :  12:56:13  Show Profile  Reply with Quote
Yes. The source and destination datatypes are the same. Yes bigint to bigint gives me an error that this is not possible inSQL 2000 or earlier. But it was odd that I was using SQL 2008 R2 SSMS. After more digging, I found out that the Insert Bulk is an undocumented feature and will not work when executed directly through client or server side. It is only available as part of some API.

Edited by - santkris4 on 06/10/2011 12:56:57
Go to Top of Page

oukuri
Starting Member

India
7 Posts

Posted - 12/18/2012 :  02:16:41  Show Profile  Reply with Quote
Hi i am trying to use 'Insert Bulk' like

Insert Bulk newpersontable select
FIRST_NM,
LST_NM,
MIDDLE_NM,
GNDR,
STU_ID,
CMP_ID,
CALNETUID,
UC_LOC,
ANID
from PERSON

But facing below error
.Net SqlClient Data Provider: Msg 4022, Level 16, State 1, Line 1
Bulk load data was expected but not sent. The batch will be terminated.

Can any one tell how to correct this

oukuri
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/18/2012 :  02:22:29  Show Profile  Reply with Quote
quote:
Originally posted by oukuri

Hi i am trying to use 'Insert Bulk' like
oukuri

You need to post your queries as a new thread

--
Chandu
Go to Top of Page

oukuri
Starting Member

India
7 Posts

Posted - 12/18/2012 :  02:26:39  Show Profile  Reply with Quote
Hi , I have started a new thread with subject 'Using Insert Bulk'

oukuri
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000