Solved

Copy table data from one db to another on same server

Posted on 2004-04-26
16
61,903 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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