Solved

Copy table data from one db to another on same server

Posted on 2004-04-26
16
61,884 Views
Last Modified: 2011-08-18
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

0
Comment
Question by:yup33
  • 5
  • 2
  • 2
  • +5
16 Comments
 
LVL 3

Expert Comment

by:edwardsearch
Comment Utility
For me, DTS is the best option

- Edward
0
 

Author Comment

by:yup33
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:bwdowhan
Comment Utility
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



 
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
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].[dbo]'  + @Tab

Go

Imran
0
 
LVL 7

Expert Comment

by:ala_frosty
Comment Utility
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
0
 
LVL 9

Expert Comment

by:auke_t
Comment Utility
I use this stored procedure:

http://vyaskn.tripod.com/code/generate_inserts.txt

Have fun!
0
 

Author Comment

by:yup33
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
>>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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

by:edwardsearch
Comment Utility
Since you don't have that much exposure, DTS is each compare with Linked Server
- Edward.P
0
 
LVL 4

Expert Comment

by:rehand
Comment Utility
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


0
 

Author Comment

by:yup33
Comment Utility
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.

0
 
LVL 4

Assisted Solution

by:rehand
rehand earned 50 total points
Comment Utility
Insert INTO Test SELECT * FROM db2name.dbo.test
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 200 total points
Comment Utility
yup33,

rehand has the correct sytax above... I didn't write that code correctly....

If you are trying to insert data from one data source into an EXISTING table use:

INSERT INTO tblName
SELECT * FROM OthertblName

If you want to create a table from your select results then you would use:

SELECT *
INTO tblName
FROM othertblName

Not only did I have the INTO clasue after the FROM statement but I was writing code to create the table... Thank you rehand for the correction...

INSERT INTO test
SELECT * FROM db2name.dbo.test

Several people have commented about creating s linked server and if you can create a linked server from where you are to the database then that is a great option. If you are going to go with the method you described, " I will upload localDB under a new name to the server alongside liveDB. ", which I took as it will be a database on the same server, then the method I am describing will work fine.

~Brian
0
 

Author Comment

by:yup33
Comment Utility
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
0
 

Author Comment

by:yup33
Comment Utility
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




0
 
LVL 5

Expert Comment

by:MichaelSFuller
Comment Utility
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,
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now