Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transfer Data from one database table to Another database table Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 11:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, 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
Post #1363795
Posted Tuesday, September 25, 2012 12:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1363802
Posted Tuesday, September 25, 2012 12:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, 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
Post #1363807
Posted Tuesday, September 25, 2012 12:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1363809
Posted Tuesday, September 25, 2012 12:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, 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
Post #1363814
Posted Wednesday, September 26, 2012 5:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
Hi,

IS there any other way?

Regards
SqlStud
Post #1364586
Posted Thursday, September 27, 2012 6:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, 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
Post #1365201
Posted Thursday, September 27, 2012 6:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1365205
Posted Friday, September 28, 2012 12:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, 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
Post #1365634
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse