T_Sql block for tranfering

Question

Post reply

T_Sql block for tranfering

nazaninahmady_sh

Default port

Points: 1465
More actions
December 16, 2008 at 4:35 am

#199000

Hello
i want to transfer data from one database to another , The Schema of Source(Table) which is in Server A is:
ID int
Title nvarchar(50)
Body ntext
Level 1 int
Level 2 int
and the schema of Destination(Table) wich is in Server B is :
ID int
Title nvarchar(50)
Body nvarchar(max)
Level 1 int
Level 2 int
but i want to modify data of 2 fields and then put them in the Server B.Destinaton(Table),
forexample in Server A.Sourse(Table) i have:
ID Title Body Level1 Level2
1 a aa 1000 2000
but in Server B.Destination (Table) i want to have:
ID Title Body Level1 Level2
1 a aa 1 2
For this Purpose i create 2 tables in Server B :L_1, L_2
both with his schema :
ID
Desc
but i dont know how to write the T_SQL :

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

INSERT INTO [Server B].[Destination].dbo.[Table] ([ID], [Title], Body, Level1, Level2)

SELECT [ID], [Title], Body, Level1/1000, Level2/1000

FROM [Server A].[Sourse].dbo.[Table]

Which server is running the statement?

Is one server linked to the other?

Do you want to transfer all of the data?

Why do you need two extra tables on server B

For this Purpose i create 2 tables in Server B :L_1, L_2

?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

thanks

i think i have to use (CAST) for transfering because in Destination the data type of Body is nvarchar(max) and in the Source it is ntext ,

a T_Sql block like this:

INSERT INTO ServerB.dbo.NewsRoom (Id, Title, Body)

SELECT a.Id, a.Title, CAST(a.Body as nvarchar (MAX))

FROM ServerA.NEWSROOM.dbo.T_Source_News a, inserted b

where a.Id = b.Id

but i have 2 another fileds that i want t change thier data and then put the modified data in ServerB.dbo.NewsRoom , pleae forget about the 2 extra tables , .

yes i want to transfer all of the data, but in each record i want to change those two fields,

regards

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nazaninahmady_sh (12/16/2008)
thanks
i think i have to use (CAST) for transfering because in Destination the data type of Body is nvarchar(max) and in the Source it is ntext ,
a T_Sql block like this:
INSERT INTO ServerB.dbo.NewsRoom (Id, Title, Body)
SELECT a.Id, a.Title, CAST(a.Body as nvarchar (MAX))
FROM ServerA.NEWSROOM.dbo.T_Source_News a, inserted b
where a.Id = b.Id
but i have 2 another fileds that i want t change thier data and then put the modified data in ServerB.dbo.NewsRoom , pleae forget about the 2 extra tables , .
yes i want to transfer all of the data, but in each record i want to change those two fields,
regards

I think you need to take a good look at your target db name

ServerB.dbo.NewsRoom (database.schema.table)

and your source

ServerA.NEWSROOM.dbo.T_Source_News (server.database.schema.table)

Are you sure they are correct?

FROM ServerA.NEWSROOM.dbo.T_Source_News a, inserted b

where a.Id = b.Id

This looks like code from a trigger?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

sorry

the correct name are:

ServerA.Source.dbo.Table1

and ServerB.Destination.dbo.Table2

but what should i do about the modifyng those 2 fileds

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

nazaninahmady_sh (12/16/2008)
sorry
the correct name are:
ServerA.Source.dbo.Table1
and ServerB.Destination.dbo.Table2
but what should i do about the modifyng those 2 fileds

Which server is running the statement?

Is one server linked to the other?

Do you want to transfer all of the data?

What is table inserted referenced above?

Do you currently have any access to either the source or the target server/table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden