Data migration issue.. - SQL Server Forums

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)

Data migration issue..

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

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

5155 Posts

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

52326 Posts

Posted - 2012-12-14 : 03:31:27

what was the actual datatype of field?

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

oukuri
Starting Member

7 Posts

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

2242 Posts

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

7 Posts

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

5155 Posts

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

oukuri
Starting Member

7 Posts

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

2242 Posts

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources