| Author |
Topic |
|
freephoneid
Yak Posting Veteran
52 Posts |
 Posted - 2010-06-08 : 18:57:16
|
| Hi, I've a table called Admin which contains id, login, password, name columns in SQL Server 2008 Since this is a newly created table, can any one tell me how to copy the table data into other machine during deployment? Please note that I need to copy only this table's data. How can I do it in SQL Server 2008. Appreciate your help!Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-06-08 : 19:14:15
|
| Can you please provide me syntax of using bcp for exporting table data of table tableA?Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-08 : 19:34:19
|
| It's a command-line executable (Start..Run..cmd):bcp db1.dbo.tbl1 out C:\Dir1\File1.csv -c -t, -T -Sserver1\instance1 -r\r\n-c means use character format (the file will be readable in a text program such as notepad).-t is the field terminator, and I have specified comma.-T means use Windows authentication. You can alternatively use -U along with -P instead of -T to use SQL authentication.-S is the server information for the source server-r is the row terminator. I specified line feed and carriage return.To then import that file, you would do this:bcp db1.dbo.tbl1 in C:\Dir1\File1.csv -c -t, -T -Sserver2\instanceB -r\r\nTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-06-08 : 19:41:15
|
| Hi tkzier, Thanks again for the info. However, let me explain my problem. When I export the data using bcp syntax which you gave me, it works fine. The file got created with records as well. Now, I imported them as well but the data is not good for password field. This field is defined as nvarchar & contains encrypted password which might have byte encoding, etc. Now, how do I export it properly so that import would also be nice with good data??Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-08 : 19:52:27
|
| I'm not sure actually as I've never dealt with moving encrypted data. Try native format (-n) instead maybe that'll preserve the data.bcp db1.dbo.tbl1 out C:\Dir1\File1.csv -n -t, -T -Sserver1\instance1 -r\r\nbcp db1.dbo.tbl1 in C:\Dir1\File1.csv -n -t, -T -Sserver2\instanceB -r\r\nI haven't used native format in a while though, so we may need to change the commands a little to get it to work. Try the above first and let me know how it goes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-06-08 : 20:24:56
|
| Yup....Thanks a lot, Tara!!! the native option worked!!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
How to copy table data
Tara Kizer