yup33
asked on
Copy table data from one db to another on same server
I have been working on a local copy of a db (localDB) and have made a lot of changes to some tables and added some as well. The live db (liveDB) is on a shared host and is still active. So there are a few tables on the liveDB that I will need to get the latest information (ie membertables) from and copy it over to localDB when the revised site goes live.
I spoke to the host and this is what i will do. I will upload localDB under a new name to the server alongside liveDB. So that both dbs are on same server. They will create new DSN for this db for the revised site to use. Once the revised site is live liveDB will be taken down and localDB with the up-to-date info is the new liveDB.
This is what i need.
------> A sql script that i can use to specify a specific table to grab data from liveDB and copy/overwrite it to localDB.
I wont have access to Enterprise Manager at the host or remote access to the dbs so they requested i send a script to accomplish this task, however, I cant seem to be able to find out how to do it. I'm guessin that if it can be done through EM then it should be able to be done through Query Analyzer somehow....
My last option is to have them send me liveDB, i restore it locally then use Enterprise Manager DTS Export Data from it to update localDB.
Thanks
I spoke to the host and this is what i will do. I will upload localDB under a new name to the server alongside liveDB. So that both dbs are on same server. They will create new DSN for this db for the revised site to use. Once the revised site is live liveDB will be taken down and localDB with the up-to-date info is the new liveDB.
This is what i need.
------> A sql script that i can use to specify a specific table to grab data from liveDB and copy/overwrite it to localDB.
I wont have access to Enterprise Manager at the host or remote access to the dbs so they requested i send a script to accomplish this task, however, I cant seem to be able to find out how to do it. I'm guessin that if it can be done through EM then it should be able to be done through Query Analyzer somehow....
My last option is to have them send me liveDB, i restore it locally then use Enterprise Manager DTS Export Data from it to update localDB.
Thanks
ASKER
So I should just get the db locally and use DTS to update it then send it back to the host? Is this the only option if i dont have remote access to their sql server?
Thanks.
Thanks.
Something to consider before trying to copy data from a table in one database to the same table in a copy of the database are the internal keys. I have run across many situations where people copy data from a table and it turns out that the foreign keys that these items point to are not the same on the other tables. If this is not an issue and you just want to wipe out the data on your new database and fill it with the data from the current db then:
from query analyzer:
USE localDB
TRUNCATE TABLE tblName
GO
SELECT *
FROM liveDB.dbo.tblName
INTO tblName
GO
This makes a lot of assumptions about the table structure not being different between the two database and the foreign key issue as stated above but if those are not issues this will clear and load the current data from liveDB into your localDB table.
~Brian
from query analyzer:
USE localDB
TRUNCATE TABLE tblName
GO
SELECT *
FROM liveDB.dbo.tblName
INTO tblName
GO
This makes a lot of assumptions about the table structure not being different between the two database and the foreign key issue as stated above but if those are not issues this will clear and load the current data from liveDB into your localDB table.
~Brian
yup33 as Edward told that DTS is best opetion.
There is another way to do this that is Linked Server
-Add a Linked Server
-Write simple Select/Insert statements to copy the data
if you want to have pass table name at runtime then you have to use Dynamic Sql (performance issue).
Any way, for this you have to create an SP taking table name as input like this
Create Procedure SP_CopyData
@Tab Varchar(255)
As
--if the structure of both the tables are same then drop the local table first
Exec SP_ExecuteSql N'Drop Table ' + @Tab
--I assume that both live and local tables have same name (you passed to SP)
--Following dynamic SQL will recreate the local table and copy data to it
Exec SP_ExecuteSql N'Select * Into '+@Tab+ ' From [LinkedServerName].[DBName
Go
Imran
There is another way to do this that is Linked Server
-Add a Linked Server
-Write simple Select/Insert statements to copy the data
if you want to have pass table name at runtime then you have to use Dynamic Sql (performance issue).
Any way, for this you have to create an SP taking table name as input like this
Create Procedure SP_CopyData
@Tab Varchar(255)
As
--if the structure of both the tables are same then drop the local table first
Exec SP_ExecuteSql N'Drop Table ' + @Tab
--I assume that both live and local tables have same name (you passed to SP)
--Following dynamic SQL will recreate the local table and copy data to it
Exec SP_ExecuteSql N'Select * Into '+@Tab+ ' From [LinkedServerName].[DBName
Go
Imran
If you want to change the table structures on their livedb give them some queries with
ALTER TABLE ... bla bla bla
If you want to update your local version with their data, then yes, I would say that DTSing the data to a file is about you best option. You could program VB to set up and execute a DTS package on their machine to push/pull the data.
Sending you their liveDb sounds a bit unneccessary.
You can get queries to execute Jobs which contain DTS packages, but I've yet to see some way to create a package from T-SQL.
HTH
ALTER TABLE ... bla bla bla
If you want to update your local version with their data, then yes, I would say that DTSing the data to a file is about you best option. You could program VB to set up and execute a DTS package on their machine to push/pull the data.
Sending you their liveDb sounds a bit unneccessary.
You can get queries to execute Jobs which contain DTS packages, but I've yet to see some way to create a package from T-SQL.
HTH
ASKER
sorry i should have stated this earlier, yes the table structures will be identical, is just the data that is outdated and needs to be copied to the localDB.
some followup comments
imrancs - how do i create a linked server?
ala frosty - should i create a DTS package locally and have them run it on the live server to move the files?
i havent done that much sql server. so a lot of this is new to me. thanks.
some followup comments
imrancs - how do i create a linked server?
ala frosty - should i create a DTS package locally and have them run it on the live server to move the files?
i havent done that much sql server. so a lot of this is new to me. thanks.
>>how do i create a linked server<<
- Server > Security > Linked Servers
- Right click on Linked Servers and add New Linked Server....
- Give the remote server name and select Sql Server (option button) as Server Type
- In the Security tab enter the local login might be 'sa' and also give the Remote user and the Remote User Password
- Click Ok
now you can use this linked server in your queries as i told in my last post.
Imran
- Server > Security > Linked Servers
- Right click on Linked Servers and add New Linked Server....
- Give the remote server name and select Sql Server (option button) as Server Type
- In the Security tab enter the local login might be 'sa' and also give the Remote user and the Remote User Password
- Click Ok
now you can use this linked server in your queries as i told in my last post.
Imran
Since you don't have that much exposure, DTS is each compare with Linked Server
- Edward.P
- Edward.P
Here are some useful links
INF: How to Use DTS Wizard to Insert Data into SQL Tables with an Identity Column
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246310
Use DTS Packages in SQL Server 2000
http://www.afs-link.com/aboutafs/Bios/Use%20DTS%20Packages%20in%20SQL%20Server%202000.pdf
INF: How to Use DTS Wizard to Insert Data into SQL Tables with an Identity Column
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246310
Use DTS Packages in SQL Server 2000
http://www.afs-link.com/aboutafs/Bios/Use%20DTS%20Packages%20in%20SQL%20Server%202000.pdf
ASKER
bwdowhan
I tried cutting and pasting into Query Analzyer.
Truncate table works, it clears out the data. But i get an error in the select.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INTO'.
USE db1name
TRUNCATE TABLE test
GO
SELECT *
FROM db2name.dbo.test
INTO test
GO
is this the correct syntax? test exists in both db's.
I tried cutting and pasting into Query Analzyer.
Truncate table works, it clears out the data. But i get an error in the select.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INTO'.
USE db1name
TRUNCATE TABLE test
GO
SELECT *
FROM db2name.dbo.test
INTO test
GO
is this the correct syntax? test exists in both db's.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
bwdowhan
Actually yes i noticed the SELECT syntax error after i posted my reply. And also noticed that I would need to use INSERT INTO after i corrected the syntax error as it said the table already existed.
So yes the dbs are on the same server and your method works great.
Thanks to everyone who replied. I will now do some research on linked servers and DTS packages.....that was a little over my head.
Thanks
Actually yes i noticed the SELECT syntax error after i posted my reply. And also noticed that I would need to use INSERT INTO after i corrected the syntax error as it said the table already existed.
So yes the dbs are on the same server and your method works great.
Thanks to everyone who replied. I will now do some research on linked servers and DTS packages.....that was a little over my head.
Thanks
ASKER
oh one thing i needed to do was
SET IDENTITY_INSERT test ON
as there were primary keys in the tables. so i had to call this line before the INSERT INTO statement.
Also i had to specify the exact columns in my INSERT INTO statement
INSERT INTO test (id, content, etc)
SELECT * FROM db2name.dbo.test
Just in case someone has the same issue.
My final code looked like:
USE db1
TRUNCATE TABLE test
GO
SET IDENTITY_INSERT test ON
GO
INSERT INTO test (id, content, etc)
SELECT id, content, etc
FROM db2.dbo.test
ORDER BY id
GO
SET IDENTITY_INSERT test OFF
GO
SET IDENTITY_INSERT test ON
as there were primary keys in the tables. so i had to call this line before the INSERT INTO statement.
Also i had to specify the exact columns in my INSERT INTO statement
INSERT INTO test (id, content, etc)
SELECT * FROM db2name.dbo.test
Just in case someone has the same issue.
My final code looked like:
USE db1
TRUNCATE TABLE test
GO
SET IDENTITY_INSERT test ON
GO
INSERT INTO test (id, content, etc)
SELECT id, content, etc
FROM db2.dbo.test
ORDER BY id
GO
SET IDENTITY_INSERT test OFF
GO
Here's a article on how to backup a single table in a database. You could modify the routine to fit your needs. http://www.databasejournal.com/features/mssql/article.php/2206571
Late,
Late,
- Edward