Copy table data from one db to another on same server

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.

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



 

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

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

I use this stored procedure:

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

Have fun!

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.

>>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

Since you don't have that much exposure, DTS is each compare with Linked Server
- 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

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.

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

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