transfer data between databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

transfer data between databases

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-22 : 18:38:20

Hi,
I have 2 databases db1 and db2. db1 has table1. db2 has table2.
- 2 fields of db2.table2 are not present in db1.table1.
- 4 fields make a primary key for db2.table2 whereas db1.table1 has only 1 field as primary key.
- is there a way to transfer data of fields from db2.table2 , except for the 2 which are not present in db1.table1.

Thanks,
Mavericky

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-22 : 18:53:02

To be specific,
These are the tables. CBLMeterValue is in db1.
CREATE TABLE [dbo].[CBLMeterValue](
[DayOfWeek] [int] NOT NULL,
[MeterID] [int] NOT NULL,
[StartDT] [datetime] NULL,
[StopDT] [datetime] NULL,
[HourEnd] [tinyint] NOT NULL,
[HourInterval] [tinyint] NOT NULL,
[KWH] [decimal](9, 2) NULL,
[UpdateDT] [datetime] NULL,
[IsBaselineProcess] [bit] NULL,
[IsDeenergized] [bit] NULL,
[HasValue] [bit] NULL,
[KWvalue] [decimal](9, 2) NULL,
[Delta] [decimal](9, 4) NULL,
[Error] [decimal](9, 4) NULL,
CONSTRAINT [PK_CBLMeterValue] PRIMARY KEY CLUSTERED
(
[DayOfWeek] ASC,
[MeterID] ASC,
[HourEnd] ASC,
[HourInterval] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

IntervalKWH is in db2.
CREATE TABLE [dbo].[IntervalKWH](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[PartitionID] [int] NOT NULL,
[UDCMeter] [varchar](30) NULL,
[BeginDT] [smalldatetime] NULL,
[EndDT] [smalldatetime] NULL,
[IntervalKWH] [decimal](9, 3) NULL,
[IsSuspect] [bit] NULL,
[UpdateDT] [datetime] NULL,
[MeterID] [bigint] NULL,
CONSTRAINT [PK_IntervalKWH] PRIMARY KEY CLUSTERED
(
[PartitionID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Now I want to transfer data from IntervalKWH. I want to data present in BeginDT,EndDT,IntervalKWH,UpdateDT,MeterID to CBLMeterValue fields: StartDT, StopDT, KWH, UpdateDT, MeterID.

Please consider this description as final. Ignore the earlier post.
Thanks,
Mavericky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 22:34:57

just do like below in db1 if db1 and db2 are in same server


USE [db1]

INSERT INTO  [dbo].[CBLMeterValue](StartDT, StopDT, KWH, UpdateDT, MeterID)
SELECT BeginDT,EndDT,IntervalKWH,UpdateDT,MeterID
FROM [db2].dbo.[IntervalKWH]

and if they're in different server add the second server as a linked server to server in which db1 exists

and change code like


USE [db1]

INSERT INTO  [dbo].[CBLMeterValue](StartDT, StopDT, KWH, UpdateDT, MeterID)
SELECT BeginDT,EndDT,IntervalKWH,UpdateDT,MeterID
FROM [Linkedservernamehere].[db2].dbo.[IntervalKWH]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 22:37:33

see the below link to understand what all you need to check for creation of linked server

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources