Solved

Copy table data from one db to another on same server

Posted on 2004-04-26
16
61,925 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
ID: 10925151
For me, DTS is the best option

- Edward
0
 

Author Comment

by:yup33
ID: 10925447
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
ID: 10925581
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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 10

Expert Comment

by:imrancs
ID: 10925591
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
ID: 10925593
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
ID: 10925607
I use this stored procedure:

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

Have fun!
0
 

Author Comment

by:yup33
ID: 10925977
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
ID: 10926439
>>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
 
LVL 3

Expert Comment

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

Expert Comment

by:rehand
ID: 10928871
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
ID: 10930832
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
ID: 10931587
Insert INTO Test SELECT * FROM db2name.dbo.test
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 200 total points
ID: 10933410
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
ID: 10933552
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
ID: 10933657
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
ID: 12206430
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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