Copy Table to Different DB
-
Our company uses an ERP system that's based on a SQL Server 2000 database with a Visual Fox Pro front end. What we're trying to do is copy selected portions of our production database into a test environment. We specifically don't want to copy the entire database into the test environment, else we'd just restore a production database backup directly into the test environment.
My supervisor created the new database from the ERP side. What happened in SQL was that a new, empty database showed up under "Databases" on our server. The production database is data01; the test environment is data51 ... as in Area 51, 'cuz we're trying to keep somewhat of a lid on our experimentation.
For a good number of the tables we need to copy from data01 to data51, DTS works fine. However, DTS repeatedly barfs when we try to copy some of the tables, including the inventory, which is one of the most important ones for our test.
The re-appearance of Steve Jones's "Designing Cross Database Queries" article was quite timely, in that it did give me a few insights, but I'm having trouble figuring out the exact syntax to copy a table in its entireity between the databases. Hopefully someone knows of a simple way to do it, because I'm starting to feel like I'm beating my head against a brick wall here.
OK ... background facts which should help with any answers ...
The names of the databases are data01 and data51. They are both located on the same server, Alpha. The specific table I'm trying to copy is inventory but there are a few others that have also proven to be problem children. And again, I'm trying to copy from data01 to data51.
TIA ...
-
Do table inventory has FK constraint? if yes load the primary table and then Inventory.
Why DTS is failing ? Look into the error ?
You can consider BCP also or even write query to populate data.
Kindest Regards,Amit Lohia
-
there is a stored procedure you can use to get all the tablenames in hierarchy order.
here's an example:
create table #tuser (type int, oname varchar(517), owner varchar(517), seq int)
insert #tuser exec sp_msdependencies null, 3, null, null, 1
you then need to import your tables in that order, witht he WHERE statemements where appropriate, in order to get your subsets of data in place.
HTH
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
There is also a stored proc I added a few weeks ago that will enable you t oscript out the insert statements for data in the tables similar to sqldump in MYSQL.
That way you can script out the ddl using enterprise manager and just script out the data you want migrated.
make sense?
Peter
Mr Peter Livesey -
Try an INSERT INTO with Column List statement it gives you control of how the table is moved into the new database. If you are in SQL Server 2005 try the link below if not run a search ofr INSERT INTO with Column List in the BOL (books online). Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms174335.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Sorry about the delayed response. I posted the question right before I left work Friday, and this is the first chance I've had today to read replies.
Amit: I don't know why DTS is failing, it just returns an "unspecified error" message. Writing a query to do the job is the very thing I'm trying to do.
Lowell and Peter: Thank you for the try. Unfortunately I don't know beans about using stored procedures, so what you're saying doesn't make sense to me. (That's my fault, not yours.
)Gift Peddie: I think you're on the same wavelength as I am, since using an INSERT INTO statement in QA had also occurred to me. I still need a little more info, though ...
1) I need to know the syntax for setting it up in QA when more than one database is involved. How do I refer to the other database so that QA knows what I mean?
2) I need a location, URL, or some way of getting to the BOL.
3) Info for you: We're running SQL Server 2000. It'll probably be a year or so before we switch to 2005.
Thanks GP and crew, this is helpful so far. The main thing I need at this point is info about addressing this via QA. Please and thank you ...
-
Did you try this?:
INSERT INTO data51..inventory
(
field1,
field2
)
SELECT
field1,
field2
FROM data01..inventory
The URL for downloading BOL:
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
i've got a decent idea how to do this, but what is your filter criteria?
i assume it is sometable.columname in(1,2,3) or sometable.columname < 3000 or something, and you want to make sure all related records related via foreign keys are brought in also.
how are you restricting which records to import in?
restoring a backup of the database is the absolute best way to copy the data, failing taht, using the Enterprise manager to import tables from another sql server database is the next best. finally, if you really want to, you can script it out in QA, but that should be a last choice option.
if you are locked into the QA method, and if you are bringing over the whole table,you can use something like JacekO said:
if there is an identity field on inventory(there most likely is
SET IDENTITY_INSERT data51..inventory ON
INSERT INTO data51..inventory
SELECT *
FROM data01..inventory
SET IDENTITY_INSERT data51..inventory OFF
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
here's an idea pending better definition of the WHERE statement: note that everything would be in foreign key hierachy as described previously.
this assumes that the foreign key column name is ALWAYS the same name as the referenced column. if it's not, you might need to add something via sp_FKEYS to get the referenced column names.
if i knew what the real WHERE statement was, the example below would filter the data based on the important column...so you'd see stuff like
' WHERE PROJECTTBLKEY IN(SELECT PROJECTTABLEKEY FROM MASTERTABLE WHERE PROJECTTBLKEY < 3000)
SET NOCOUNT ON
declare @importantColumn varchar(30)
declare @mastertable varchar(30)
declare @wherestatement varchar (30)
set @importantColumn = 'SOMETBLKEY'
set @mastertable='SOMETABLE'
set @wherestatement = ' < 3000'
--DROP TABLE #TUSER
create table #tuser (type int, oname varchar(517), owner varchar(517), seq int,HasIdentity int,HasImportantColumn int)
insert #tuser(type, oname, owner, seq ) exec sp_msdependencies null, 3, null, null, 1
update #tuser set HasIdentity = 1
where oname in(select sysobjects.name from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and syscolumns.autoval is not null)
update #tuser set HasImportantColumn = 1
where oname in(select sysobjects.name from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and syscolumns.name= 'somekey')
select TOP 5
case when isnull(HasIdentity,0) = 1 then 'SET IDENTITY_INSERT ' + oname + 'ON' + char(13) else '' end
+ ' INSERT INTO data51..' + oname + char(13)
+ ' SELECT * FROM data01..' + oname
+ case when isnull(HasImportantColumn,0) = 1 then 'WHERE ' + @importantColumn + ' IN(select ' + @importantColumn + ' FROM ' + @mastertable + ' WHERE ' + @importantColumn + ' ' + @wherestatement + ')' + CHAR(13)
ELSE '' END
+ case when isnull(HasIdentity,0) = 1 then 'SET IDENTITY_INSERT ' + oname + 'ON' + char(13) else '' end + CHAR(13)
+ case when isnull(HasIdentity,0) = 1 then 'SET IDENTITY_INSERT ' + oname + 'OFF' + char(13) else '' end + char(13)
from #tuser
example results from QA with the Results in text option:
SET IDENTITY_INSERT AGENCY_WEB_USER_AGREEMENTON
INSERT INTO data51..AGENCY_WEB_USER_AGREEMENT
SELECT * FROM data01..AGENCY_WEB_USER_AGREEMENTSET IDENTITY_INSERT AGENCY_WEB_USER_AGREEMENTON
SET IDENTITY_INSERT AGENCY_WEB_USER_AGREEMENTOFF
INSERT INTO data51..CMADDR
SELECT * FROM data01..CMADDR
SET IDENTITY_INSERT CMMAILINGON
INSERT INTO data51..CMMAILING
SELECT * FROM data01..CMMAILINGSET IDENTITY_INSERT CMMAILINGON
SET IDENTITY_INSERT CMMAILINGOFF
INSERT INTO data51..CMMLADDR
SELECT * FROM data01..CMMLADDR
SET IDENTITY_INSERT COMPACTIONTYPEON
INSERT INTO data51..COMPACTIONTYPE
SELECT * FROM data01..COMPACTIONTYPESET IDENTITY_INSERT COMPACTIONTYPEON
SET IDENTITY_INSERT COMPACTIONTYPEOFF
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sorry, Lowell, to me SQL scripts are even less comprehensible than Greek.
However, for copying tables from data01 to data51, WHERE statements and filters aren't an issue since we want to bring the tables over in their entirety. I am going to have a go with the INSERT INTO setup when I have a chance. I have a whole network to support here -- two networks, on some days -- not just the ERP's database.
For us, copying the entire database is a last resort. Even with copying only selected tables, we're still going to have some trimming to do, but far less than if we copied the whole database. So, to answer an earlier question, yes I am pretty much locked into using QA. And DTS, since it does seem to work for the majority of the tables we need to copy. It's just on the big important tables with lots of fields where DTS seems more inclined to cough its cookies than copy the data.
Thanks to everyone for the input. I am going try again with QA, using the info I've gleaned so far, and I'll drop another line in this thread telling how it goes.
---------
MSSQL not found. A)bort, R)etry, H)ug teddy bear and cry?
-
Just an update for the benefit of anyone who responded and/or is watching this topic:
By using a slightly different tack in DTS, I got all of the tables I needed to copy to data51 except for one. Unfortunately, the problem child is my old friend the inventory table.
The change I made was at the point in the wizard where it asks what you want to copy. Rather than "Copy table(s) and view(s) from the source database," I chose "Copy objects and data between SQL Server databases." Like I said, that worked for everything but the inventory table. With the inventory table, I got an error message that read:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table 'dbo.inventory' because it is being referenced by a FOREIGN KEY constraint.
If anyone knows the whys and wherefores of this error message, and how to get the table to copy, I'd be happy to hear from them.
In the meantime, thanks to everyone who responded, both to me and to others who have posted questions about DTS. In fact, another person's question was what gave me the idea of changing the copy method. Which goes to show that more folks may benefit from the question-and-answer forum style than you may realize at first.
Thanks again,
Geoff
--
Only an absolutist deals in Sith lore.
-
you might try: select * into data51.dbo.inventory from data01.db.inventory
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply