Data Transform Script

Question

Post reply

Data Transform Script

karthik M

One Orange Chip

Points: 29139
More actions
August 31, 2007 at 9:16 am

#118341

Hi Experts,
I want to move data from a database(sql2000) to another database(sql2000) which resides in same server.
I dont want to use DTS Import.Export wizard, instead of i perform this transformation job by a sql script.
Can any expert give me this sql script ?
Regards
Karthik

karthik

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if it's just one or two tables, then you'd simply use something like

SELECT *

INTO MYTABLENAME

FROM OTHERDATABASE.DBO.MYTABLENAME

[WHERE <condition to exclude unwanted data>].

if it is more than that, a script will most likely forget a lot of things that DTS wouldn't.

you REALLY should use DTS.

as an example, suppose you script out all the tables. unless you took the time to make sure the scripts are in foreign key hierarchy order, you'll get errors.

then youll need to move the data. again, in hierarchy order, unless you disable all constraints, move the data, and then reenable.

then there indexes that are not part of the table definition to consider, along with views, procedures and functions.

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!

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Let us assume we have 25 master tables without any relationship.

in this case i dont want to use DTS package.

I want to execute a script which will insert all the table values from one db to another db.

Regards

Karthik

karthik

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

You may "pretend" to Import/Export data. At a point, Save, Schedule, and Replicate Window, check Save DTS Package , then, Structured Storaged File.

Is it what you want?

karthik M One Orange Chip Points: 29139 More actions · Answer 4

No i dont want to use DTS import/export wizard. Because the same script will be called from .net.

So I need a pure sql script which will take all the records from (db1) to (db2).

Regards

Karthik

karthik

Lowell SSC Guru Points: 323518 More actions · Answer 5

creating the tables and migrating the data?

if exists(select name from db1.dbo.sysobjects where name='mastertable1') drop table db1.dbo.mastertable1

select * into db1.dbo.mastertable1 from db2.dbo.mastertable1

just moving new data?

insert into db1.dbo.mastertable1

select * from db2.dbo.mastertable1

left outer join db1.dbo.mastertable1 on db2.dbo.mastertable1.ID = db1.dbo.mastertable.ID

where db1.dbo.mastertable.ID is null

assuming the tables are in place and truncating the old data and replacing with new?

truncate table db1.dbo.mastertable1

select * into db1.dbo.mastertable1 from db2.dbo.mastertable1

select the example you need, and repeat for all 25 tables

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!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

For what it's worth - not wanting DTS "because it would be called from .NET" isn't a good reason. DTS packages can easily be called from .NET, and with some more effort, edited, created or deleted for that matter.

As far as getting a T-SQL script - Lowell seems to have given you some workable choices.

Sorry - I got to ask - are you SURE we're not doing your homework? Just seems like a lot of oddball questions one would get asked as part of a quiz/ assignment.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?