| Author |
 Topic  |
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
 Posted - 12/13/2012 : 06:07:19
|
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
5152 Posts |
Posted - 12/13/2012 : 10:07:57
|
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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 12/14/2012 : 03:31:27
|
what was the actual datatype of field?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
oukuri
Starting Member
India
7 Posts |
Posted - 12/18/2012 : 02:12:43
|
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
1430 Posts |
Posted - 12/18/2012 : 02:20:48
|
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 |
|
|
|
oukuri
Starting Member
India
7 Posts |
Posted - 12/18/2012 : 05:40:29
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/18/2012 : 07:35:12
|
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 |
|
|
|
oukuri
Starting Member
India
7 Posts |
Posted - 12/19/2012 : 01:15:04
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/19/2012 : 02:54:54
|
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 |
|
|
| |
Topic |
|
Data migration issue..
