Sql-server – Using data from other database to populate a new database

sql server

I have a SQL Server database DB1 and want to move data from DB1 to DB2. But only want some of the data to be moved.

I want all the rows from DB1 that have a catParent that is the same as I Id from DB2. And I only want the DB1.catDescription = DB2.Name and DB1.catLink = DB2.URL

How do I manage that? Do I need a sub select query inside my insert query and how is the syntax for this?

DB1

CREATE TABLE [dbo].[category](
    [catID] [bigint] IDENTITY(1,1) NOT NULL,
    [catDescription] [varchar](50) NOT NULL,
    [catParent] [bigint] NULL,
    [catSortOrder] [bigint] NOT NULL,
    [catLink] [varchar](255) NOT NULL,
    [catShow] [tinyint] NULL,
    [catTarget] [varchar](3) NOT NULL,
    [catAssign] [varchar](3) NOT NULL,
 CONSTRAINT [primary key] PRIMARY KEY CLUSTERED 
(
    [catID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DB2

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Guid] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Description] [ntext] NULL,
    [AssignArticles] [bit] NULL,
    [URL] [nvarchar](max) NULL,
    [Target] [int] NOT NULL,
    [Publish] [bit] NOT NULL,
    [ParentCategoryId] [int] NOT NULL,
    [Sort] [int] NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [LevelVal] [int] NULL,
    [PageLayoutId] [int] NOT NULL,
    [M03PictureId] [int] NOT NULL,
    [Sitemap] [bit] NOT NULL,
    [Search] [bit] NOT NULL,
    [LinkText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Best Answer

This should do it.

update DB2.[dbo].[Categories]
set
    DB2.[dbo].[Categories].Name = DB1.[dbo].[category].catDescription,
    DB2.[dbo].[Categories].URL = DB1.[dbo].[category].catLink
from
    DB2.[dbo].[Categories] inner join
    DB1.[dbo].[category] on DB2.[dbo].[Categories].ID = DB1.[dbo].[category].catParent
Related Question