BULK INSERT from Select statement

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

SQL Server 2008 Forums

Transact-SQL (2008)

BULK INSERT from Select statement

Author

Topic

santkris4
Starting Member

10 Posts

Posted - 2011-06-07 : 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!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-07 : 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 - 2011-06-07 : 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
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-07 : 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 - 2011-06-07 : 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
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-07 : 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 - 2011-06-10 : 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.

oukuri
Starting Member

7 Posts

Posted - 2012-12-18 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 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

7 Posts

Posted - 2012-12-18 : 02:26:39

Hi , I have started a new thread with subject 'Using Insert Bulk'

oukuri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources