| Author |
 Topic  |
|
|
santkris4
Starting Member
10 Posts |
 Posted - 06/07/2011 : 13:11:28
|
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
1608 Posts |
Posted - 06/07/2011 : 13:49:30
|
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 |
 |
|
|
santkris4
Starting Member
10 Posts |
Posted - 06/07/2011 : 14:00:20
|
| 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. |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/07/2011 : 14:29:23
|
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 |
|
|
|
santkris4
Starting Member
10 Posts |
Posted - 06/07/2011 : 15:01:54
|
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. |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/07/2011 : 16:21:56
|
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 |
|
|
|
santkris4
Starting Member
10 Posts |
Posted - 06/10/2011 : 12:56:13
|
| 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 |
|
|
|
oukuri
Starting Member
India
7 Posts |
Posted - 12/18/2012 : 02:16:41
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/18/2012 : 02:22:29
|
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 |
|
|
|
oukuri
Starting Member
India
7 Posts |
Posted - 12/18/2012 : 02:26:39
|
Hi , I have started a new thread with subject 'Using Insert Bulk'
oukuri |
|
|
| |
Topic |
|
BULK INSERT from Select statement
