sql server data warehouse: Data transfer between sql server 2000 databases -- sql server data warehouse

Data transfer between sql server 2000 databases

Balaji
3/16/2007 9:11:03 AM

sql server data warehouse:

I have a SQL Server 2000 Enterprise Edition database TestDB1 with 23 tables
that gets populated by replication from a mainframe server. (Inserts,
Updates, Deletes)

Now we have to move the data from TestDB1 to TestDB2 on the same SQL Server
machine. (Same instance of SQL Server) TestDB2 will be used by an in-house
Java based reporting tool.

But the column names of tables in TestDB2 will be different from the columns
names of tables in TestDB1. The data types will be the same. The date/time
values in the TestDB1 database tables are in two columns. This must be
converted to DateTime data type when inserting into tables in TestDB2.

Does anyone have suggestions on how to do this as the column names are
different?

The TestDB1 is a OLTP with data coming from Mainframe. We guess about 1 or 2
million transactions a day happening on TestDB1.

Re: Data transfer between sql server 2000 databases

TheSQLGuru
3/26/2007 9:25:07 AM

For the simple part of scripting the inserts, use SSMS or some other tool
that can script table SELECT statements and use that for the inserts. You
don't need to bother with field names in the INSERT mytable SELECT field1,
field2, ... from myothertable syntax as long as the fields are in the same
order and same datatype.

For the Date/time concatenation, simply do that. Cast the date field and
the time fields each saparately to a varchar (if not already that datatype)
and Concatenate those two items together and cast to datetime on the insert
statement. Voila!

--
TheSQLGuru
President
Indicium Resources, Inc.

[quoted text, click to view]