September 24, 2012 at 11:45 pm
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
September 25, 2012 at 12:22 am
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 25, 2012 at 12:28 am
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
September 25, 2012 at 12:38 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 25, 2012 at 12:49 am
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
September 26, 2012 at 5:47 am
Hi,
IS there any other way?
Regards
SqlStud
September 27, 2012 at 6:46 am
sqlstud (9/25/2012)
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
September 27, 2012 at 6:51 am
Have a look here and see how you get on. It does something similar to what you require.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2012 at 12:40 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply