script to build table in sql server baseds on oracle table
I am trying to build a script that will build a table in my SQL Server 2008 and I wanted to base it on the table that is currently in Oracle
in oracle I can write something like
create table temp1 as
select * from Data_source_table
I want to achieve the same thing, but I want the creation of the table in SQL Server 2008.
This is becasue Oracle will still hold a bunch of business Logic and functions that maniplute the data , and columns on the orcale table may change. So I want to minimize the coding needed when a change occurs in Oracle, I could re-run the script and it would
a) drop the table in SQL SERVER
b) rebuild the table based on the structure in ORACLE
c) populate the table in SQL SERVER based on the date in ORACLE
d) rebuild and run stats on the indexes in SQL SERVER
I am at a loss as to who to do this.
I have built the SQL Server database,
in oracle I can write something like
create table temp1 as
select * from Data_source_table
I want to achieve the same thing, but I want the creation of the table in SQL Server 2008.
This is becasue Oracle will still hold a bunch of business Logic and functions that maniplute the data , and columns on the orcale table may change. So I want to minimize the coding needed when a change occurs in Oracle, I could re-run the script and it would
a) drop the table in SQL SERVER
b) rebuild the table based on the structure in ORACLE
c) populate the table in SQL SERVER based on the date in ORACLE
d) rebuild and run stats on the indexes in SQL SERVER
I am at a loss as to who to do this.
I have built the SQL Server database,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
while this did not solve my issue, it did give me other ideas on how ot solve the issue and what not to do, which is just as helpfull
ASKER
Here is the situation:
I have a number of tables that are being developed and poplutated in Oracle. And rather then each time creating a or manually changing the table structure in SQL Server, i wanted to run a script to build tables in SQL Server based on the structer of the tables that had just been developed in ORACLE and then populate the data that is in the Oracle table to the SQL Server table.
This would help me reduce the time it is taking to manually go into the wizad and each time make the adjustments needed and then to repopulate the data.
I am trying to see if there is a script i could run from a dos prompt.
For oracle, I would just write the appropraiate commands in a sql file and then run the following command
sqlplus scott/tiger@test @c:/testfiles/buildtableSc