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
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Merge table from DB 1 to DB 2 ?

Author  Topic 

sentinelace
Starting Member

34 Posts
Posted - 2012-04-11 : 13:17:17
I have a few tables I would like to merge from one database to another database. can I do this on a schedule with Management studio?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-11 : 19:11:20
yes you can.
can you provide table structure. you can create a sproc that handles that for you.
are you interested only in inserts or also deletes and updates?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-12 : 11:25:20
What is the best way to display the structure? How do I create a sproc?
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-12 : 12:27:08
in ssms right click on table and then choose
Script table as and the choose CREATE To --> new query editor window
once you do that for all your tables we will talk about sprocs

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-12 : 13:35:44
quote:
Originally posted by yosiasz

in ssms right click on table and then choose
Script table as and the choose CREATE To --> new query editor window
once you do that for all your tables we will talk about sprocs

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion


First table:

USE [TESTDATABASE]
GO

/****** Object:  Table [dbo].[TEST_DAT]    Script Date: 04/12/2012 13:32:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TEST_DAT](
	[F_TEST] [int] NOT NULL,
	[F_CRTM] [int] NULL,
	[F_EDTM] [int] NULL,
	[F_TSGP] [int] NOT NULL,
	[F_TYPE] [int] NULL,
	[F_NAME] [varchar](32) NOT NULL,
	[F_TEXT] [varchar](256) NULL,
	[F_ABBR] [varchar](8) NULL,
	[F_MTYP] [int] NULL,
	[F_UTYP] [int] NULL,
	[F_DFGP] [int] NULL,
	[F_FCTR] [float] NULL,
	[F_USER] [int] NULL,
	[F_DSBL] [int] NULL,
	[F_RFC] [int] NULL,
 CONSTRAINT [TEST_DAT_PK] PRIMARY KEY CLUSTERED 
(
	[F_TEST] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [TEST_DAT_UQ] UNIQUE NONCLUSTERED 
(
	[F_NAME] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TEST_DAT]  WITH CHECK ADD  CONSTRAINT [TEST_DAT_FK1] FOREIGN KEY([F_TSGP])
REFERENCES [dbo].[TEST_GRP] ([F_TSGP])
GO

ALTER TABLE [dbo].[TEST_DAT] CHECK CONSTRAINT [TEST_DAT_FK1]
GO


Second table:
USE [TESTDATABASE]
GO

/****** Object:  Table [dbo].[TEST_DAT_T]    Script Date: 04/12/2012 13:34:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TEST_DAT_T](
	[F_TEST] [int] NULL,
	[F_CRTM] [int] NULL,
	[F_EDTM] [int] NULL,
	[F_TSGP] [int] NULL,
	[F_TYPE] [int] NULL,
	[F_NAME] [varchar](32) NULL,
	[F_TEXT] [varchar](256) NULL,
	[F_ABBR] [varchar](8) NULL,
	[F_MTYP] [int] NULL,
	[F_UTYP] [int] NULL,
	[F_DFGP] [int] NULL,
	[F_FCTR] [float] NULL,
	[F_USER] [int] NULL,
	[F_DSBL] [int] NULL,
	[F_RFC] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO




Third table:
USE [TESTDATABASE]
GO

/****** Object:  Table [dbo].[TEST_GRP]    Script Date: 04/12/2012 13:35:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TEST_GRP](
	[F_TSGP] [int] NOT NULL,
	[F_CRTM] [int] NULL,
	[F_EDTM] [int] NULL,
	[F_NAME] [varchar](32) NOT NULL,
	[F_TEXT] [varchar](256) NULL,
	[F_ABBR] [varchar](8) NULL,
	[F_FCTR] [float] NULL,
	[F_USER] [int] NULL,
	[F_DSBL] [int] NULL,
	[F_RFC] [int] NULL,
 CONSTRAINT [TEST_GRP_PK] PRIMARY KEY CLUSTERED 
(
	[F_TSGP] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [TEST_GRP_UQ] UNIQUE NONCLUSTERED 
(
	[F_NAME] 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

SET ANSI_PADDING OFF
GO




4th table:
USE [TESTDATABASE]
GO

/****** Object:  Table [dbo].[TEST_GRP_T]    Script Date: 04/12/2012 13:36:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TEST_GRP_T](
	[F_TSGP] [int] NULL,
	[F_CRTM] [int] NULL,
	[F_EDTM] [int] NULL,
	[F_NAME] [varchar](32) NULL,
	[F_TEXT] [varchar](256) NULL,
	[F_ABBR] [varchar](8) NULL,
	[F_FCTR] [float] NULL,
	[F_USER] [int] NULL,
	[F_DSBL] [int] NULL,
	[F_RFC] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-13 : 10:32:32
ok some tables have 10 columns and other 15. do you want to merge all 4 tables?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-13 : 11:06:22
yes. I want to merge everything in those tables from db1 to db2
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-13 : 11:26:51
1. create a view

create view dbo.sentinelace
as
SELECT [F_CRTM]
      ,[F_EDTM]
      ,[F_TSGP]
      ,[F_TYPE]
      ,[F_NAME]
      ,[F_TEXT]
      ,[F_ABBR]
      ,[F_MTYP]
      ,[F_UTYP]
      ,[F_DFGP]
      ,[F_FCTR]
      ,[F_USER]
      ,[F_DSBL]
      ,[F_RFC]
  FROM [dbo].[TEST_DAT_T]
UNION  
SELECT [F_CRTM]
      ,[F_EDTM]
      ,[F_TSGP]
      ,[F_TYPE]
      ,[F_NAME]
      ,[F_TEXT]
      ,[F_ABBR]
      ,[F_MTYP]
      ,[F_UTYP]
      ,[F_DFGP]
      ,[F_FCTR]
      ,[F_USER]
      ,[F_DSBL]
      ,[F_RFC]
  FROM [dbo].[TEST_DAT_T]
UNION
SELECT [F_CRTM]
      ,[F_EDTM]
      ,[F_TSGP]
      ,NULL [F_TYPE]      
      ,[F_NAME]
      ,[F_TEXT]
      ,[F_ABBR]
      ,NULL [F_MTYP]
      ,NULL [F_UTYP]
      ,NULL [F_DFGP]      
      ,[F_FCTR]
      ,[F_USER]
      ,[F_DSBL]
      ,[F_RFC]
  FROM [dbo].[TEST_GRP]  
UNION
SELECT [F_CRTM]
      ,[F_EDTM]
      ,[F_TSGP]

      ,NULL [F_TYPE]      
      
      ,[F_NAME]
      ,[F_TEXT]
      ,[F_ABBR]

      ,NULL [F_MTYP]
      ,NULL [F_UTYP]
      ,NULL [F_DFGP]      
      
      ,[F_FCTR]
      ,[F_USER]
      ,[F_DSBL]
      ,[F_RFC]
  FROM [dbo].[TEST_GRP_T]


2. How often do you want this merge to run?
3. Do you want only inserts or also deletes and updates of destination table?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
[/code]

2. How often do you want this merge to run?
3. Do you want only inserts or also deletes and updates of destination table?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-16 : 10:47:06
do I just create a query? I get "Msg 208, Level 16, State 1, Procedure sentinelace, Line 18
Invalid object name 'DASupport.dbo.TEST_DAT_T'"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-16 : 15:59:35
Change that to your database

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-17 : 10:23:28
Now I get "Msg 208, Level 16, State 1, Procedure testdatabase, Line 17
Invalid object name 'dbo.TEST_DAT_T'."

I assume this is a query?
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-17 : 10:46:17
yes this is. Are you running this on the TESTDATABASE database? if not it will give you that error because it cannot find such a table in the database you are running this query on/

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-17 : 11:09:02
doh. That's what I was doing wrong. It completed successful. Now what do I do with that? :)
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-17 : 12:26:17
1. USE TESTDATABASE
2. select top 1000 * from dbo.sentinelace (do you like what you see, visually verify the data being returned)
3. Do you want only inserts or also deletes and updates of destination table?



<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-17 : 13:15:24
once I execute, how do I view it? it completes successful. Also, I am not sure what all the data is or isn't. I just know I want all of it merged to the other database. I am not sure what you mean by deletes and inserts. Wouldn't that include everything? (which is what I need). So I really don't know what the data looks like visually. I just need it all :)
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-17 : 14:15:40
in SSMS what do you see when you do select top 1000 * from dbo.sentinelace ? you should see data details. The problem of having it merged indiscriminately is that you will have duplicates. for you I would go with truncate destination table and then reload, to keep things simple/


<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-20 : 08:54:25
I will have to confirm this data is good. If this data looks good, what is the next step?
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-20 : 10:04:44
next step create store procedure. read up about that, create a stored procedure that use the above view and get back to us with your stored sproc.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-04-30 : 16:30:32
quote:
Originally posted by yosiasz

next step create store procedure. read up about that, create a stored procedure that use the above view and get back to us with your stored sproc.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion


I see alot of this topic but not exactly what I am trying to do. Any tips on getting the procedure created?
Go to Top of Page

sentinelace
Starting Member

34 Posts
Posted - 2012-05-03 : 09:17:54
bump
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site