February 16, 2010 at 5:08 am
Hi people.
Am still new in SQL.
Can anyone help me,I want to move data from one table to another table in a different servers.How can I accomplish that please help me.
Thanks
February 16, 2010 at 5:11 am
many options are avasilable
use import export wizard(for one time movement)
u can use ssis package too(For daily moving data)
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 16, 2010 at 5:12 am
You can use SQL Server Import and Export Wizard. Right click on your source database, select Tasks->Export. The wizard will guide you.
February 16, 2010 at 6:48 am
I am with Sanket and Fazalul, use the SQL Server wizard driven Import / export of its a one time process if its going to be a recurring process, build SSIS package which gives more flexibility to it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 16, 2010 at 12:06 pm
it really depends on how easily you can connect the servers. if you have linked servers and can query directly from one to the other, i'd just do it in t-sql. for example:
insert into ServerB.dbo.Customers (ID, Name, desc)
select ID, Name, desc
from ServerA.dbo.Customers
someone correct me if i am wrong?
February 16, 2010 at 12:49 pm
If you use the wizard, you can choose to append to or delete the existing data. With linked server, manually delete the existing data first if you need to.
February 16, 2010 at 1:11 pm
homebrew01 (2/16/2010)
If you use the wizard, you can choose to append to or delete the existing data. With linked server, manually delete the existing data first if you need to.
I agree, keep it as simple as you can.
EnjoY!
February 16, 2010 at 2:20 pm
Hi Everyone,
I'm wanting to do something similar in SQL 2005. From Server A (in the network) I want to connect to Server B (in it's own DMZ); while on server B I want to execute and SSIS package that takes data from a table, I want to bring that data back to Server A.
When I try to use the import/export wizard on server B it will not let me enter my server A as the destination.
Does this mean I have to do it some other way and if so how can I do this? And what kind of connection do I have to set up between server A and Server B since server B is in it's own DMZ.
I'd really appreciate your input as I'm not sure what I need to do.
Diane
February 17, 2010 at 12:30 pm
Diane Rayzer (2/16/2010)
Hi Everyone,I'm wanting to do something similar in SQL 2005. From Server A (in the network) I want to connect to Server B (in it's own DMZ); while on server B I want to execute and SSIS package that takes data from a table, I want to bring that data back to Server A.
When I try to use the import/export wizard on server B it will not let me enter my server A as the destination.
Does this mean I have to do it some other way and if so how can I do this? And what kind of connection do I have to set up between server A and Server B since server B is in it's own DMZ.
I'd really appreciate your input as I'm not sure what I need to do.
Diane
Just guessing, but would the IP adres work instead of server name ? Also, maybe identical SQL logins instead of windows accounts ?
February 17, 2010 at 2:21 pm
Possibly... I'm in the initial stages trying to figure out how to define the connection first. Right now I'm defining a 'linked server'. I hope this the correct thing to do?????????
February 17, 2010 at 4:13 pm
Diane Rayzer (2/16/2010)
Hi Everyone,I'm wanting to do something similar in SQL 2005. From Server A (in the network) I want to connect to Server B (in it's own DMZ); while on server B I want to execute and SSIS package that takes data from a table, I want to bring that data back to Server A.
When I try to use the import/export wizard on server B it will not let me enter my server A as the destination.
Does this mean I have to do it some other way and if so how can I do this? And what kind of connection do I have to set up between server A and Server B since server B is in it's own DMZ.
I'd really appreciate your input as I'm not sure what I need to do.
Diane
What is the error message you got? You should be able to import\export if you enter login details correct.
EnjoY!
February 18, 2010 at 1:57 am
Hi Diane,
I've done exactly this, LAN to DMZ, by doing what Homebrew01 said - using identical SQL logins to configure the linked server. In some cases I've needed to use the IP Address, others it works with the name.
Cath
February 18, 2010 at 8:02 am
I never completed it import/export wizard since it would not take the destination server name. I figured I better get the connection working first.
February 18, 2010 at 8:10 am
Cath,
Ok so in the linked server definition under security you used a Local sql logon (impersonate)? And that created the connection for you? Do you have a firewall and if so did you have to open the port on the firewall, a non-default (port other than 1433)? If port other than 1433, was just specifying the port enough and did you have to do something else in SQL to recognize this other port?
THank you very much,
Diane
February 18, 2010 at 8:51 am
The only option I could get to work was 'For a login not in the list ...' and 'connect using these credentials'. I know that's not the best, but it's a very restricted account, with its permissions limited to just the task it needs to do. I tried 'impersonate' with the same credentials, but authentication always failed.
The server's on a default port, but as to the firewall, sorry, I don't know about that.
Cath
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply