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)
 Data migration issue..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 12/13/2012 :  06:07:19  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
I'm migrating data from Ms Access to Sql server 2008 using Linked Server.

I have a table with 25lakhs + records

Using Insert into .. Select statement i'm moving the data from linked server to Sql server destination table.

All the records are moved sucessfully, but only 3 records are appended with '?' in a Varchar(255) column.

Can any one tell why it happens and how to avoid?


Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  10:07:57  Show Profile  Reply with Quote
I was trying to post an example, but the post did not come through correctly because of unicode characters.

What I was going to say was that this is probably because you have unicode characters in those 3 rows. So either you have to use NVARCHAR/NCHAR data type, or discard the data in those rows. If you are able to identify the rows that are exhibiting this behavior, you should be able to look in the source data and find what kind of text those 3 rows contain.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  03:31:27  Show Profile  Reply with Quote
what was the actual datatype of field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

oukuri
Starting Member

India
7 Posts

Posted - 12/18/2012 :  02:12:43  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
2218 Posts

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

Hi i am trying to use 'Insert Bulk' like ............
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


Try this....
Insert INTO Bulk newpersontable
SELECT FIRST_NM, LST_NM, MIDDLE_NM, GNDR,STU_ID,CMP_ID,CALNETUID,UC_LOC, ANID
from PERSON



--
Chandu
Go to Top of Page

oukuri
Starting Member

India
7 Posts

Posted - 12/18/2012 :  05:40:29  Show Profile  Reply with Quote
What i want to do is bulk insert , in the previous thread i have seen syntax using Insert Bulk , but when i am using the same i am facing an 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.'
since this is Level 16 error which is due to some mistake . can any one help me to figure out the issue . There no much documentation about Insert Bulk command in net.:(

oukuri
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  07:35:12  Show Profile  Reply with Quote
quote:
Originally posted by oukuri

What i want to do is bulk insert , in the previous thread i have seen syntax using Insert Bulk , but when i am using the same i am facing an 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.'
since this is Level 16 error which is due to some mistake . can any one help me to figure out the issue . There no much documentation about Insert Bulk command in net.:(

oukuri

If your code is exactly as you posted: You also need to tell the query WHERE to get the data from - a file. The expectation is that the table schema would match the data available in the file. So it would be something like this

BULK INSERT newpersontable
FROM 'f:\orders\lineitem.csv'
If you are trying to insert the data from a database table to another database table, don't use bulk insert.

Bulk insert also allows you to specify additional options - there are details and examples here: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Edited by - sunitabeck on 12/18/2012 07:36:59
Go to Top of Page

oukuri
Starting Member

India
7 Posts

Posted - 12/19/2012 :  01:15:04  Show Profile  Reply with Quote
Yes , I am trying to pull data from table in another schema. Why can't we use Bulk Insert between tables in different schemas or the same.

oukuri
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/19/2012 :  02:54:54  Show Profile  Reply with Quote
Bulk insert is used to insert data from a flat file( or .csv files), but not from another database..

>> Yes , I am trying to pull data from table in another schema.
If instance and database is same and schema is dirrferebt, then use simple INSERT statement as follows:
INSERT INTO schemaName2.TableName2
SELECT yourColumns
FROM schemaName1.TableName1



--
Chandu
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.08 seconds. Powered By: Snitz Forums 2000