SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transfer Data from one database table to Another database table


Transfer Data from one database table to Another database table

Author
Message
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
Hi,

I have two databases – DBSample1,DBSample2
Both databases contains same table names(TabSample1 and TabSample11) and same design

Need to copy the data from one database table to another database table ( ie)
DBSample1.TabSample1 to DBSample2.TabSample1,
DBSample1.TabSample11 to DBSample2.TabSample11

Database : DBSample1

CREATE TABLE [dbo].[TabSample1](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL,
[TypeId] [nvarchar](max) NULL,
[UserName] [nvarchar](max) NULL,
[CreatedDateTime] [datetime] NULL,
[CompletedDateTime] [datetime] NULL,
[Boolean] [bit] NULL,
)
CREATE TABLE [dbo].[TabSample11](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[EngineerName] [nvarchar](max) NULL,
[CustomerName] [nvarchar](max) NULL,
[CustomerAddressLine1] [nvarchar](max) NULL,
[CustomerAddressLine1] [nvarchar](max) NULL,
[CustomerAddressLine3] [nvarchar](max) NULL,
[CustomerAddressLine3] [nvarchar](max) NULL,
[CustomerPostCode] [nvarchar](max) NULL,
[CustomerPostCode] [nvarchar](max) NULL,
[CustomerTelephoneNumber] [nvarchar](max) NULL,

)
Database : DBSample2

CREATE TABLE [dbo].[TabSample1](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL,
[TypeId] [nvarchar](max) NULL,
[UserName] [nvarchar](max) NULL,
[CreatedDateTime] [datetime] NULL,
[CompletedDateTime] [datetime] NULL,
[Boolean] [bit] NULL,
)
CREATE TABLE [dbo].[TabSample11](
[pKey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SasId] [nvarchar](max) NULL,
[EngineerName] [nvarchar](max) NULL,
[CustomerName] [nvarchar](max) NULL,
[CustomerAddressLine1] [nvarchar](max) NULL,
[CustomerAddressLine2] [nvarchar](max) NULL,
[CustomerAddressLine3] [nvarchar](max) NULL,
[CustomerAddressLine4] [nvarchar](max) NULL,
[CustomerPostCode] [nvarchar](max) NULL,
[CustomerTelephoneNumber] [nvarchar](max) NULL,

)

Sample Data:

insert into TabSample1 values('sdf','werwer','s233','xzfdsdf',12/11/2011,14/11/2011,'TRUE')
insert into TabSample11 values ('werwer','sdfsdffd','ertrtet','dfgdfdffggf','fghfghfgh','sdgdfgfggf','uyiuiui','y5555','4587423963')

While transferring the data from one table to another table, it should be appended to existing rows.

Table DBSample1.TabSample1 get on an average 75 records daily, we just need to copy this 75 records to DBSample2.TabSample1 without deleting the previous days records
So its kind of incremental upload to DBSample2.TabSample1 .


Regards
SqlStud
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18634 Visits: 20449
If you can, I would recommend that you change the PK definitions in the target database - they should be defined as int, but NOT identity.

Why? So that the PKs in the source table can be inserted into the target table, meaning that you can do a direct match from one to the other.

Is it safe to assume that there will be no updates to existing rows in the tables?

A script along the following lines should do it (if you make the change I suggested), assuming DbSample1 is source and DbSample2 target (untested and incomplete):

declare @MaxId int

--Get the MaxId from table1 in target database
select @MaxId = (select max(pKey) from DBSample2.dbo.TabSample1)

--Insert all the rows from table1 in source database whose ID is greater.
insert DbSample2.dbo.TabSample1(pKey, col1, col2, ...)
select pKey, Col1, Col2, ...
from DbSample1.dbo.TabSample1
where pKey > @MaxId




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
Thanks Phil..

Forgot to inform that i need to use SSIS 2005. So that i can schedule it in SQL Server Agent job to run on daily basis.

How do we transfer data using SSIS 2005

Regards
SqlStud
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18634 Visits: 20449
sqlstud (9/25/2012)
Thanks Phil..

Forgot to inform that i need to use SSIS 2005. So that i can schedule it in SQL Server Agent job to run on daily basis.

How do we transfer data using SSIS 2005

Regards
SqlStud


Oops - I didn't notice the forum - I should have guessed.

Are the databases on different servers? If not, SSIS is overkill here - why not just create a stored proc which does the transfer and schedule that in SQL Agent?

If they are on different servers and you don't want to use a linked-server approach, the SSIS process is similar:

1. Set up a MaxId variable with package scope.
2. Use an ExecuteSQL task to populate the variable with the max ID from the target table.
3. Create a dataflow which selects the rows from the source table using the maxid as a parameter to limit what is selected and map it to the target table.

Job done.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
[b]

Oops - I didn't notice the forum - I should have guessed.

Are the databases on different servers? If not, SSIS is overkill here - why not just create a stored proc which does the transfer and schedule that in SQL Agent?

If they are on different servers and you don't want to use a linked-server approach, the SSIS process is similar:

1. Set up a MaxId variable with package scope.
2. Use an ExecuteSQL task to populate the variable with the max ID from the target table.
3. Create a dataflow which selects the rows from the source table using the maxid as a parameter to limit what is selected and map it to the target table.

Job done.


Thanks Phil.

Yes.. Databases are on different servers and don't want to use a linked-server approach

If possible, could you please provide me the screen shot how to implement your steps?


Regards
SqlStud
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
Hi,

IS there any other way?

Regards
SqlStud
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
sqlstud (9/25/2012)
[b]

Oops - I didn't notice the forum - I should have guessed.

Are the databases on different servers? If not, SSIS is overkill here - why not just create a stored proc which does the transfer and schedule that in SQL Agent?

If they are on different servers and you don't want to use a linked-server approach, the SSIS process is similar:

1. Set up a MaxId variable with package scope.
2. Use an ExecuteSQL task to populate the variable with the max ID from the target table.
3. Create a dataflow which selects the rows from the source table using the maxid as a parameter to limit what is selected and map it to the target table.

Job done.


Thanks Phil.

Yes.. Databases are on different servers and don't want to use a linked-server approach

If possible, could you please provide me the screen shot how to implement your steps?


Regards
SqlStud



Hi Phil,

Could you please provide me the steps to proceed?

Regards
SqlStud
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18634 Visits: 20449
Have a look here and see how you get on. It does something similar to what you require.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sqlstud
sqlstud
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 237
Phil Parkin (9/27/2012)
Have a look here and see how you get on. It does something similar to what you require.



Thanks Phil...

But we need to transfer the data from source to target directly without using staging table.

In the given URL, they have loaded to staging table and then they loaded to target table.

I have tried without using staging table, record in source get loaded to target.

Again i ran the package, the entire records got loaded into target without considering the MaxID

Regards
SqlStud
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search