sql server programming:
Looking at your DDL you do not have a FK reference from TableB to TableA. If
so, how do you intend to find the links? Do you mean to parse the name
field?
If you do have a relationship between these two tables, the easiest way to
perform this task would be just to move the records from TableA into TableB,
then update the FK relationship and get rid of TableA all together.
SET IDENTITY_INSERT ON
GO
INSERT INTO TableB SELECT [id], [name], 0 FROM TableA
GO
ALTER TABLE [TableB] ADD
CONSTRAINT [FK_TableB_TableB] FOREIGN KEY
( [parent] ) REFERENCES [TableB] ( [id] )
GO
Again, this assumes you HAVE a FK relationship. If not, then you need to
rework your SELECT statement to whatever you are using now to join on the
tables (parsing the name I guess).
HTH,
John Scragg
[quoted text, click to view] "Adam Knight" wrote:
> Hi all,
>
> I have the two tables (parent - child) i want to merge into one.
>
> 1) myTableA
> - id
> - name
>
> 2) myTableB
> - id
> - name
> - parent (references myTableA.id)
>
> 3) myTableC
> - id
> - name
> - parent
>
> The contents of myTableA & myTableB are the tables to be merged into
> myTableC.
>
> Logic:
>
> INSERT PARENT ITEMS: MAIN QUERY
> A) Insert all 'name' attributes in myTableA Into myTable C.
> Also set the parent attribute for these entries to 0.
> myTableC.id is an IDENTITY attribute therefore increments
>
> INSERT CHILD ITEMS: SUB QUERY?
> B) Retrieve all name attributes from myTableB WHERE myTableB.parent =
> myTableA.id
> INSERT name attributes into myTableC.
> SET parent attribute to IDENTITY value (myTableC.id) created in MAIN
> QUERY
>
> I hope this make sense, and would appreciate any help!!!
>
> Cheers,
> Adam
>
> DDL:
>
> CREATE TABLE [dbo].[myTableA](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_myTableA] PRIMARY KEY CLUSTERED
> (
> [id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[myTableB](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_myTableB] PRIMARY KEY CLUSTERED
> (
> [id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[myTableC](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [parent] [int] NOT NULL,
> CONSTRAINT [PK_myTableC] PRIMARY KEY CLUSTERED
> (
> [id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> SET IDENTITY_INSERT ON 'myTableA'
> INSERT INTO myTableA('id','name') VALUES(1,'name a')
> INSERT INTO myTableA('id','name') VALUES(2,'name b')
> INSERT INTO myTableA('id','name') VALUES(3,'name c')
>
> SET IDENTITY_INSERT ON 'myTableB'
> INSERT INTO myTableB('id','name') VALUES(1,'child of name a')
> INSERT INTO myTableB('id','name') VALUES(2,'child of name b')
> INSERT INTO myTableB('id','name') VALUES(3,'child of name c')
> INSERT INTO myTableB('id','name') VALUES(4,'child of name c')
>
> RESULT SHOULD BE:
> myTableC:
> id name parent
> 1 'name a' 0
> 2 'child of name a' 1
> 3 'name b' 0
> 4 'child of name b' 3
> 5 'name c' 0
> 6 'child of name c' 5
> 7 'child of name c' 5
>
>
Hi all,
I have the two tables (parent - child) i want to merge into one.
1) myTableA
- id
- name
2) myTableB
- id
- name
- parent (references myTableA.id)
3) myTableC
- id
- name
- parent
The contents of myTableA & myTableB are the tables to be merged into
myTableC.
Logic:
INSERT PARENT ITEMS: MAIN QUERY
A) Insert all 'name' attributes in myTableA Into myTable C.
Also set the parent attribute for these entries to 0.
myTableC.id is an IDENTITY attribute therefore increments
INSERT CHILD ITEMS: SUB QUERY?
B) Retrieve all name attributes from myTableB WHERE myTableB.parent =
myTableA.id
INSERT name attributes into myTableC.
SET parent attribute to IDENTITY value (myTableC.id) created in MAIN
QUERY
I hope this make sense, and would appreciate any help!!!
Cheers,
Adam
DDL:
CREATE TABLE [dbo].[myTableA](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_myTableA] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[myTableB](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_myTableB] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[myTableC](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[parent] [int] NOT NULL,
CONSTRAINT [PK_myTableC] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT ON 'myTableA'
INSERT INTO myTableA('id','name') VALUES(1,'name a')
INSERT INTO myTableA('id','name') VALUES(2,'name b')
INSERT INTO myTableA('id','name') VALUES(3,'name c')
SET IDENTITY_INSERT ON 'myTableB'
INSERT INTO myTableB('id','name') VALUES(1,'child of name a')
INSERT INTO myTableB('id','name') VALUES(2,'child of name b')
INSERT INTO myTableB('id','name') VALUES(3,'child of name c')
INSERT INTO myTableB('id','name') VALUES(4,'child of name c')
RESULT SHOULD BE:
myTableC:
id name parent
1 'name a' 0
2 'child of name a' 1
3 'name b' 0
4 'child of name b' 3
5 'name c' 0
6 'child of name c' 5
7 'child of name c' 5
