Data Migration Example

This is a demonstration regarding How to Insert or Update Data From One Table to Another


For Test Purpose Create Two Databases


Test1 and Test2.


Run This Query To Create A Table



USE [test1]
GO


SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[table1](
[id] [int] NOT NULL,
[name1] [varchar](50) NOT NULL,
[add1] [varchar](50) NULL
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO
----------------------------------------------------------------------------------------------------------------------

USE [test2]
GO


SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[table2](
[id] [int] NOT NULL,
[name2] [varchar](50) NOT NULL,
[add2] [varchar](50) NULL
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO


---------------------------------------------------------------------------------------------------------------

Now Its Time To Fill Out The First Table 
---------------------------------------------------------------------------------------------------------------

insert into test1.dbo.table1 values(1,'fname1','address1')

insert into test1.dbo.table1 values(2,'fname2','address2')
insert into test1.dbo.table1 values(3,'fname3','address3')
insert into test1.dbo.table1 values(4,'fname4','address4')
insert into test1.dbo.table1 values(5,'fname5','address5')
-----------------------------------------------------------------------------------------------------------------
Now Comes The Real Stuff , That is to fill out the other Table with data of first one.
-----------------------------------------------------------------------------------------------------------------

insert test2.dbo.table2(id,name2)
select ID,name1 from test1.dbo.table1

----------------------------------------------------------------------------------------------------------------
I have Filled only 2 columns and Want To Fill Out last Column With Update Query
----------------------------------------------------------------------------------------------------------------

UPDATE
    test2.dbo.table2
SET
     test2.dbo.table2.add2 = test1.dbo.table1.add1
     
     FROM
  test2.dbo.table2
INNER JOIN
  test1.dbo.table1  
ON
    test2.dbo.table2.id = test1.dbo.table1.id
-----------------------------------------------------------------------------------------------------------------
Here It Is.. And You Have Successfully Migrated The Data Without Any Fuss.