Question about move of bigint variable from one table into nvarchar variable in other table

Question

Post reply

Question about move of bigint variable from one table into nvarchar variable in other table

bobznkazoo

Mr or Mrs. 500

Points: 526
More actions
September 13, 2011 at 10:16 pm

#244877

I need to move several hundred records across DBs from one table into another table. Each table has the same number of fields, but the ID fields are different. The data will be exported from one table into a CSV file which is then imported into the other table on a different server. Both DBs are SQL Server 9.0.4xxx
Table 1 ID field:
ID( PK, bigint, not null)
Table 2 ID field:
ID(nvarchar(50), null)
Can I move these rows without any problems?
Thanks,
Bob

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

That depends, do you know that the values are unique across systems? If not, you will hit problems.

Bigger question for me, why are you storing numbers in a varchar field?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

You can copy from bigint (not null) column to nvarchar(50) (null). It may not work the other way. I assume you intend to concatenate the ID value with some char type value. Otherwise, as it is already pointed out, it will be absurd to store bigint values in a nvarchar column. As a thumb rule, always use the right data types. It will help you a lot.

https://sqlroadie.com/

bobznkazoo Mr or Mrs. 500 Points: 526 More actions · Answer 3

Grant Fritchey (9/14/2011)
That depends, do you know that the values are unique across systems? If not, you will hit problems.
Bigger question for me, why are you storing numbers in a varchar field?

Thanks for the answers.

I didn't create the systems and have only been tasked to make the move so can't answer why it was set up with this format. It turns out that one system is development and the other is production.

I agree that it is absurd and problematic to run things this way. There is most likely an application down the line that needs character values but that is just an assumption. I just wanted to know what problems I may have with the transfer.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

bobznkazoo (9/14/2011)
Grant Fritchey (9/14/2011)
That depends, do you know that the values are unique across systems? If not, you will hit problems.
Bigger question for me, why are you storing numbers in a varchar field?
Thanks for the answers.
I didn't create the systems and have only been tasked to make the move so can't answer why it was set up with this format. It turns out that one system is development and the other is production.
I agree that it is absurd and problematic to run things this way. There is most likely an application down the line that needs character values but that is just an assumption. I just wanted to know what problems I may have with the transfer.

Production and development are completely different? Oh, that has to make production upgrades fun.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

Grant Fritchey (9/14/2011)
That depends, do you know that the values are unique across systems? If not, you will hit problems.

Unless I missed something or the OP left out some information, the nvarchar(50) ID column on Table2 is not the primary key. If this is the case, the bigint ID values from Table1 (the PK) will be unique in that table, but inserting them into the nvarchar(50) ID column in Table2 will succeed even if the same values already exist in Table2. That could cause a whole 'nother set of problems!

Jason Wolfkill