Copy over a single table in sql server 2008
-
Is this a one time or daily. If daily think about replication. Can the two systems talk to each other? If you must export to a table one option would be .CSV which would not loose the data like Excel will.
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
-
Why not use the SQL Server Import and Export Wizard[^]?
Veni, vidi, abiit domum
-
The environments don't talk to each other - the servers are physically located in different states. And if i export to excel, I am worried about losing some of the data, as I have specified above. Thanks.
vkEmerson wrote:
The environments don't talk to each other - the servers are physically located in different states.
Which is why I suggested the Export/Import Wizard.
vkEmerson wrote:
And if i export to excel, I am worried about losing some of the data, as I have specified above.
Why do you want to use Excel? And if you are storing dates and times in your database as strings you are heasding for trouble.
Veni, vidi, abiit domum
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
vkEmerson wrote:
What would be the best way to copy over the table?
Create a backup of the database on server A. Restore the backup on server B under a new name. Do a "SELECT INTO" from the new database into the target-table.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
vkEmerson wrote:
What would be the best way to copy over the table?
Create a backup of the database on server A. Restore the backup on server B under a new name. Do a "SELECT INTO" from the new database into the target-table.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
You will be surprised at to how many restrictions smaller size companies have :) The database size is huge, and no FTP services set up. Not allowed. If I try to copy the file from production server, with a simple copy and paste onto my server, it takes for ever and sometimes just disconnects. And to top this, we cannot do many things on the production server, since they don't want to slow down the website it feeds to :(
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
Have you thought about using a linked server connection? Creating Linked Servers (SQL Server Database Engine)[^]
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
You will be surprised at to how many restrictions smaller size companies have :) The database size is huge, and no FTP services set up. Not allowed. If I try to copy the file from production server, with a simple copy and paste onto my server, it takes for ever and sometimes just disconnects. And to top this, we cannot do many things on the production server, since they don't want to slow down the website it feeds to :(
How about reversing the process first? On the source server, create a new empty database and use
SELECT INTOto copy just the table(s) you want. Back up the (much smaller) database, copy the backup to the destination server, restore, andSELECT INTOthe destination table.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You will be surprised at to how many restrictions smaller size companies have :) The database size is huge, and no FTP services set up. Not allowed. If I try to copy the file from production server, with a simple copy and paste onto my server, it takes for ever and sometimes just disconnects. And to top this, we cannot do many things on the production server, since they don't want to slow down the website it feeds to :(
vkEmerson wrote:
You will be surprised at to how many restrictions smaller size companies have
If the restriction makes the request technically impossible, it's usually the restriction that's changed - not the request.
vkEmerson wrote:
If I try to copy the file from production server, with a simple copy and paste onto my server
How about writing your own BITS client[^]?
vkEmerson wrote:
they don't want to slow down the website it feeds to
Then why are they allowing copy/paste? One will "have to" write files on the server if the server is to be updated :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
-
Hi, We have two different production environments, in literally two different locations physically, with the same table layouts. For purposes of data warehouse, the two environments. I want to copy over data from server 1 to server 2. The table has only 4000 rows. The date format of this data is of the form
2013-10-01 10:28:35.560
. so, i worry if I try to export this data into excel from server 1 and try to import this data back into server 2's table if I would lose any of the information. What would be the best way to copy over the table? Thanks so much!
you can Save the .mdf and .ldf from the origin, and then ATTACH them to the target
