Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]
share|improve this question
2  
Is DB1 on the same instance as DB2? Can you provide DDL statements for the two tables? –  Shawn Melton Feb 4 '13 at 9:01
    
Yes they are on the same instance, updating the post –  Darkmage Feb 4 '13 at 9:09
    
Added the Create statments to the post –  Darkmage Feb 4 '13 at 9:15
2  
Now, what have you tried so far? –  Shawn Melton Feb 4 '13 at 10:17
4  
You have more columns in target than source, and they're also NOT NULL columns. Where are you getting the other values from? (This will affect the query you need to use). –  Stuart Moore Feb 4 '13 at 11:42

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
1  
Thanks. My mistake. I've updated to full qualified names. –  Hugo Mendes Feb 6 '13 at 11:47
    
Instead of using <br> tags, you should use the {} button over the textarea (which basically inserts four spaces before each row). –  dezso Feb 6 '13 at 11:52

You can refer to tables on the same instance using full name which is: database.schema.table. For this you can do something like:

USE DB2
GO

INSERT INTO Categories(<insert columns>)
SELECT <select columns from cat table>
FROM DB1.dbo.category AS cat
INNER JOIN Categories
ON cat.catDescription = Categories.Name and cat.catLink = Categories.URL
AND cat.catParent = <your condition>

I'll let you sort out the insert and select columns, since mapping between database columns is not not trivial and you didn't provider enough information. I also don't understand what you mean by "I want all the rows from DB1 that have a catParent that is the same as I Id from DB2.", so you can fill in that condition too.

If you need to move ID too (which is IDENTITY column), remember to execute before INSERT statement SET IDENTITY_INSERT Categories ON and after you finish SET IDENTITY_INSERT Categories OFF.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.