Import data with bulk insert - 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

General SQL Server Forums

New to SQL Server Programming

Import data with bulk insert

Author

Topic

MariusC
Starting Member

16 Posts

Posted - 2014-06-25 : 09:04:02

Hi all,

I have imported data in my table using the bulk insert command. I was supposed to fill specific columns of my table with that data so I used a view to put them in the column I wanted.
The table looks like this now:

id | id_param | val_param
+-----------+--------------+
1 | no_tel | 742062141
2 | sex | 1
3 | age | 23
4 | no_tel | 765234157
5 | sex | 1
6 | age | 34

When I want to select only the val_param that is=1 for the id_param=sex using this interogation:

select * from bd_rox where id_param='sex' and val_param='1'

it returns no value and I don`t know why.The wanted result should look like this:

id | id_param | val_param
+-----------+--------------+-
2 | sex | 1
5 | sex | 1

Tusharp86
Starting Member

9 Posts

Posted - 2014-06-25 : 09:14:00

can you please try following query
select * from bd_rox where ltrim(rtrim(rid_param))='sex' and ltrim(rtrim(val_param))='1'
some white space may be gives wrong result, please check it first

MariusC
Starting Member

16 Posts

Posted - 2014-06-25 : 09:17:38

I have tried it. Still the same result :(

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 09:22:19

It could be some other hidden characters in your data. See what it contains by converting a sample row to varbinary

select cast(id_param as varbinary), cast(val_param as varbinary) from bd_rox where id = 2

Then compare the results with what you expect it to be

select cast('sex' as varbinary), cast(1 as varbinary)

If the column is unicode, you may see some zero padding, but still you should be able to eyeball them and see if you have any extraneous characters in the data in your table.

MariusC
Starting Member

16 Posts

Posted - 2014-06-25 : 09:36:48

So for the parameter 'sex' I got the same result. For '1' when I use the first select it return the value = 0x0931 that is, as you said, not good. What should I do in this case ?
I assume that all the data from the column val_param is the same as this '1'.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 12:07:28

Look at the text file that you imported to see if it is corrupt, or if it has data that is different from what you expect. You might need to look at it using a text editor that can show you the binary - TextPad, for example.

Tusharp86
Starting Member

9 Posts

Posted - 2014-06-26 : 09:26:10

Hi I have same problem , In my case some bad character (hidden) is present in the string so remove that hidden value first by using update query

here i posted to how the find the bad character or hidden character in the string.

You can assign @nstring with your table column, run following query that will give you the each and every character unicode if any hidden character present in your string it will be detected. once it deteclted than update that character with the space

DECLARE @nstring NVARCHAR(100)
SET @nstring =N' TeSt'

DECLARE @position INT
SET @position = 1

DECLARE @CharList TABLE (
Position INT,
UnicodeChar NVARCHAR(1),
UnicodeValue INT
)

WHILE @position <= DATALENGTH(@nstring)
BEGIN
INSERT @CharList
SELECT @position as Position
,CONVERT(nchar(1),SUBSTRING(@nstring, @position, 1)) as UnicodeChar
,UNICODE(SUBSTRING(@nstring, @position, 1)) as UnicodeValue
SET @position = @position + 1
END

SELECT * FROM @CharList

Please update if you solve this.

Thanks
Tushar Pedhadiya

MariusC
Starting Member

16 Posts

Posted - 2014-06-30 : 05:12:19

@Tusharp86 I have solved the problem by importing data from another format. I imported the data at first from a txt file and the spaces from txt where the problem. Using csv solved it.

You`re solution was also suitable.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-30 : 05:20:37

Trailing spaces are rarelry the cause of this problem.
Either you have a case sensitive or binary collation, or there are other problems.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MariusC
Starting Member

16 Posts

Posted - 2014-06-30 : 10:15:58

I just supposed that the spaces were the problem. Didn`t check to see for sure because I solved the problem another way.

Thanks all for the reply !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources