| Author |
 Topic  |
|
|
sentinelace
Starting Member
USA
34 Posts |
 Posted - 04/11/2012 : 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
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/11/2012 : 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 |
 |
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/12/2012 : 11:25:20
|
| What is the best way to display the structure? How do I create a sproc? |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/12/2012 : 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 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/12/2012 : 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
|
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/13/2012 : 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 |
Edited by - yosiasz on 04/13/2012 10:53:24 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/13/2012 : 11:06:22
|
| yes. I want to merge everything in those tables from db1 to db2 |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/13/2012 : 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
|
Edited by - yosiasz on 04/16/2012 15:59:13 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/16/2012 : 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'" |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/16/2012 : 15:59:35
|
Change that to your database
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/17/2012 : 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? |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/17/2012 : 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 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/17/2012 : 11:09:02
|
| doh. That's what I was doing wrong. It completed successful. Now what do I do with that? :) |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/17/2012 : 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 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/17/2012 : 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 :) |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/17/2012 : 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 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/20/2012 : 08:54:25
|
| I will have to confirm this data is good. If this data looks good, what is the next step? |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1610 Posts |
Posted - 04/20/2012 : 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 |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 04/30/2012 : 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? |
|
|
|
sentinelace
Starting Member
USA
34 Posts |
Posted - 05/03/2012 : 09:17:54
|
| bump |
|
|
| |
Topic |
|
|
|
Merge table from DB 1 to DB 2 ?
