Question about SQL Server Replication

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

SQL Server Administration (2008)

Question about SQL Server Replication

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-01-10 : 01:38:22

I've 2 databases. It's test1, and test2

In test1, my table and data as following,


USE [test1]
GO
/****** Object:  Table [dbo].[roles]    Script Date: 01/10/2012 14:26:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[roles](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[descrp] [varchar](100) NOT NULL,
 CONSTRAINT [PK_roles] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [roles_UQ1] UNIQUE NONCLUSTERED 
(
	[descrp] 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
SET IDENTITY_INSERT [dbo].[roles] ON
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (8, N'Carian Pembayar')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (4, N'Carian Pengguna')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (12, N'Carian Syarikat')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (5, N'Pembayar Baru')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (6, N'Pembayar Edit')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (7, N'Pembayar Hapus')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (1, N'Pengguna Baru')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (2, N'Pengguna Edit')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (3, N'Pengguna Hapus')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (9, N'Syarikat Baru')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (10, N'Syarikat Edit')
INSERT [dbo].[roles] ([idx], [descrp]) VALUES (11, N'Syarikat Hapus')
SET IDENTITY_INSERT [dbo].[roles] OFF

In test2, my table as following


USE [test2]
GO
/****** Object:  Table [dbo].[rolesSummary]    Script Date: 01/10/2012 14:30:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[rolesSummary](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[myGroup] [varchar](50) NOT NULL,
	[myNo] [int] NOT NULL,
 CONSTRAINT [PK_rolesSummary] PRIMARY KEY CLUSTERED 
(
	[idx] 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

This is my requirement
1. I want the summary of the roles in test1 will replicate into test2
2. The expected result as following,
if test1


[test1]
roles
idx | descrp
------------------------
8	Carian Pembayar
4	Carian Pengguna
12	Carian Syarikat
5	Pembayar Baru
6	Pembayar Edit
7	Pembayar Hapus
1	Pengguna Baru
2	Pengguna Edit
3	Pengguna Hapus
9	Syarikat Baru
10	Syarikat Edit
11	Syarikat Hapus

then


[test2]
rolesSummary
idx | myGroup              | myNo
----------------------------------------
1	Carian                 | 3
2	Pembayar               | 3
3	Pengguna               | 3
4	Syarikat               | 3

If user do the T-SQL as following in test1,


insert into roles(descrp) values('Pengguna Agung')

then


[test2]
rolesSummary
idx | myGroup              | myNo
----------------------------------------
1	Carian                 | 3
2	Pembayar               | 3
3	Pengguna               | 4
4	Syarikat               | 3

If replication technology can do that. Please tell me, how to do that.

I'm currently only know 'How to replicate a table'

Please help me

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-01-10 : 05:32:47

Help me ... :(

Sachin.Nand

2937 Posts

Posted - 2012-01-10 : 08:32:24

You cannot do it by replication.You will have to write some SP or a trigger that populates the tables in the second database.

After Monday and Tuesday even the calendar says W T F ....

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-01-10 : 09:53:47

oh. tq sir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources