Copy records from SELECT with relationnal tables

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

Transact-SQL (2008)

Copy records from SELECT with relationnal tables

Author

Topic

Scal
Starting Member

24 Posts

Posted - 2011-04-29 : 05:12:33

Hi;
I have the following tables and structures:

Table "Actions"
- ID
- Command
Table "Cases"
- ID
- Name
Table "Cases_To_Actions"
- ID
- Actions_ID (relation to column ID of table "Actions")
- Cases_ID (relation to column ID of table "Cases")

What I would like to do is:
- copy the case ID 999 from Actions (I store the new ID with SCOPE_IDENTITY inside a variable "NewCaseID")
- copy the Actions records that belong to case id 999
- copy the Cases_To_Actions records that belong to case id 999 but assigning "NewCaseID" to column "Cases_ID" and the new actions id into "Actions_ID"

I would be able to do it but with a cursor to loop all actions, is it possible to do it without a cursor in such situation?

Thanks

Scal
Starting Member

24 Posts

Posted - 2011-05-02 : 08:12:54

Hi;
no one has a hint, advice or idea for this?

Thanks;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-02 : 08:24:07

Maybe you can make our life easier giving
create sample table statements
insert sample data statements
example to see the wanted result in relation to the sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

Scal
Starting Member

24 Posts

Posted - 2011-05-03 : 05:32:24

Sure, here goes the tables design:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Actions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Command] [nvarchar](250) NOT NULL,
[Argument1] [nvarchar](1000) NOT NULL,
[Argument2] [nvarchar](1000) NOT NULL,
[OrderNumber] [int] NULL,
CONSTRAINT [PK_Actions] 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]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Cases](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](150) NOT NULL,
[Properties] [nvarchar](250) NULL,
[IsActive] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
CONSTRAINT [PK_Cases] 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]

GO

ALTER TABLE [dbo].[Cases] ADD CONSTRAINT [DF_Cases_IsActive] DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[Cases] ADD CONSTRAINT [DF_Cases_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Cases_To_Actions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Cases_ID] [int] NOT NULL,
[Actions_ID] [int] NOT NULL,
CONSTRAINT [PK_Cases_To_Actions] 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]

GO

And some data:
INSERT INTO [Actions] ([Command],[Argument1],[Argument2],[OrderNumber]) VALUES ('command 1','arg1 1','arg2 1',1)
INSERT INTO [Actions] ([Command],[Argument1],[Argument2],[OrderNumber]) VALUES ('command 2','arg1 2','arg2 2',2)
INSERT INTO [Actions] ([Command],[Argument1],[Argument2],[OrderNumber]) VALUES ('command 3','arg1 3','arg2 3',3)

INSERT INTO [Cases] ([Name],[Properties],[IsActive],[IsDeleted]) VALUES ('Case 1','Properties 1',1,0)
INSERT INTO [Cases] ([Name],[Properties],[IsActive],[IsDeleted]) VALUES ('Case 2','Properties 2',1,0)

INSERT INTO [Cases_To_Actions] ([Cases_ID],[Actions_ID]) VALUES (1, 1)
INSERT INTO [Cases_To_Actions] ([Cases_ID],[Actions_ID]) VALUES (1, 2)
INSERT INTO [Cases_To_Actions] ([Cases_ID],[Actions_ID]) VALUES (1, 3)

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-03 : 07:13:56

So far so good, tahnk you.
Do you have also a wanted result for us?

No, you're never too old to Yak'n'Roll if you're too young to die.

Scal
Starting Member

24 Posts

Posted - 2011-05-04 : 05:27:47

The wanted result, as specified into the initial post is to do a bulk copy of existing records for a given case, to a new one. To illustrate this, a inner join would give something like this:

Case 1 | Properties 1 | command 1
Case 1 | Properties 1 | command 2
Case 1 | Properties 1 | command 3

I want to copy records from all tables with new relations; that would do something similar to:

INSERT INTO Cases (SELECT * FROM Cases WHERE ID = 1)
@newCaseID = @@IDENTITY

INSERT INTO Actions (SELECT * FROM Actions AS a INNER JOIN Cases_To_Actions AS c2a ON a.ID = c2a.Actions_ID WHERE c2a.Cases_ID = 1)
-- get all new IDs of actions here

INSERT INTO Cases_To_Actions (SELECT * FROM Cases_To_Actions WHERE Cases_ID = 1) --but changing the Cases_ID to @newCaseID AND the Actions_ID to the ones being created from above statement

I know how I would do it with a cursor, but wonder if it's not possible to do a build copy where SQL would take care of the new IDs created.
See what I mean?

Thanks!

Scal
Starting Member

24 Posts

Posted - 2011-05-09 : 18:40:45

Anyone? If not, cursor it will be :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-09 : 20:32:51

OMG, you used the "C" word!!

Is your usage pattern always going to be copying a single row from the Case table along with its associated rows from the other two tables? OR, will you need to copy a group of cases?

Also, is the Cases_To_Actions a many-to-many relationship, or one to many relationship? If it is one-to-many, I am wondering why you would even need the join table.

In any case, here is my attempt at doing this without cursors. Mind you, I am trying this only to keep you from using cursors until someone else (webfred, where are you when you are really needed!?!) can post a more elegant and complete solution!!


declare @newCaseID int;
INSERT INTO Cases SELECT Name,Properties,IsACtive,IsDeleted FROM Cases WHERE ID = 1;
set @newCaseID = scope_identity();

INSERT INTO Actions 
output @newCaseID,inserted.id into CAses_to_actions (Cases_Id, Actions_Id)
SELECT Command,ARgument1,ARgument2,OrderNumber
FROM Actions AS a INNER JOIN Cases_To_Actions AS c2a 
ON a.ID = c2a.Actions_ID WHERE c2a.Cases_ID = 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-10 : 03:00:32

quote:
(webfred, where are you when you are really needed!?!)

I am not really needed if you are online

No, you're never too old to Yak'n'Roll if you're too young to die.

Scal
Starting Member

24 Posts

Posted - 2011-05-10 : 11:09:05

Thanks sunitabeck;
hehehe, yes I did use the "c" word .. at least I got someone's attention ;)

Indeed, Cases_To_Actions is many-to-many and only copy one case at the time, but I will more likely need to do copying group as a feature when the application evolves.

Thanks for the SQL, will try ASAP!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources