Output Command Question

Question

Output Command Question

mbender

SSCrazy

Points: 2595
More actions
August 12, 2010 at 7:25 am

#226020

I have a series of tables that we want to duplicate the information from one table to another. Each table has a primary key and a foreign key from the other table. The basic structure of the table is
Create Table dbo.MainInformation(
MainPkey int IDENTITY(1,1) NOT NULL,
Name varchar(100)
)
Create Table dbo.Locations(
LocationPKey int IDENTITY(1,1) NOT NULL,
MainPkey int not null,
LocationName varchar(100),
LocationAddress varchar(100)
)
Create Table dbo.Equipment(
EquipmentPKey int IDENTITY(1,1) NOT NULL,
LocationPKey int NOT NULL,
PartNumber varchar(100),
Quantity int
)
Insert into dbo.MainInformation
(Name)
Values ('Bob Smith')
Insert into dbo.Locations
(MainPkey,LocationName,LocationAddress)
Values (1,'George Smith','1234 anywhere')
Insert into dbo.Locations
(MainPkey,LocationName,LocationAddress)
Values (1,'Joe Smith','14 anywhere')
Insert into dbo.Locations
(MainPkey,LocationName,LocationAddress)
Values (1,'Sam Smith','234 anywhere')
Insert into dbo.Locations
(MainPkey,LocationName,LocationAddress)
Values (1,'Hank Smith','124 anywhere')
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(1,'widget-1',1)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(1,'widget-2',1)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(2,'widget-1',11)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(2,'widget-12',1)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(3,'widget-5',10)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(3,'widget-25',10)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(4,'widget-6',10)
insert into Equipment
(LocationPKey,PartNumber,Quantity)
Values
(4,'widget-5',10)
Want I want to do is given the MainPkey from the primary table duplicate the records all the way down the tree. I can do the first two tables using temptables and the output command.
Declare @MyTempTable Table(
PrimaryKeyId Int
)
Insert into MainInformation (Name)
output inserted.MainPkey into @MyTempTable
Select Name from MainInformation Where MainPkey=1
Declare @MyLocationTempTable Table(
LocationPrimaryKeyId Int
)
Insert Into Locations
(MainPkey, LocationName, LocationAddress)
Output inserted.LocationPkey into @MyLocationTempTable
Select (Select PrimaryKeyId from @MyTempTable) as MainPkey, LocationName, LocationAddress
from Locations
Where MainPkey=1
I get to this point then i'm stuck, I need to insert into the equipment table using the newly created location primary keys and i'm unsure how. I can't use a subquery because it returns more then one value. My first thought was linking in the temp location table and the main location in the join to pull the equipment, but I don't have a way to tie it in.
Any help on this would be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Assuming you are trying to copy all the data from one set of tables to another, just create your new tables and then turn on identity_insert for each new table. Then just do mass insert. Then turn off identity_insert

set IDENTITY_INSERT [YourTable] ON

after you are done

set IDENTITY_INSERT [YourTable] OFF

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbender SSCrazy Points: 2595 More actions · Answer 2

The thing is after I insert I need to take that primary key and insert it into a different table because of the relationship as foreign key. All three tables have a relationship to each other that I need to replicate.

Balaji-472504 Old Hand Points: 359 More actions · Answer 3

Hi,

You can do this using composable DML statments: Try this code ..here i am inserting multiple values and the same i am capturing in a temp table, so that i can use it further:

CREATE TABLE T1 ( t1_No int identity(1,1) , Name varchar(10))

CREATE TABLE T2 ( t2_No int identity(1,1) , Name varchar(10),t1_No int )

INSERT INTO T1 VALUES ('AAAA')

-- Temp table to hold the newly inserted keys and values

CREATE TABLE #TempKeys(No int, Name varchar(10))

INSERT INTO T1 (Name)

OUTPUT inserted.t1_No , inserted.Name

INTO #TempKeys(No,Name)

SELECT 'BBBB'union SELECT 'CCCCC'

SELECT * FROM #TempKeys

--Reddy Balaji C.

mbender SSCrazy Points: 2595 More actions · Answer 4

mbender

SSCrazy

Points: 2595

August 13, 2010 at 7:06 am

#1206592

Thanks I will give that a try

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

I meant that you could turn identity_insert on for all 3 of your tables, then you just insert your data and turn identity_insert back off. That would keep all the relations intact with no problem.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbender SSCrazy Points: 2595 More actions · Answer 6

ok that doesn't quite work from i need to do, because what i want to capture in the temp table is the new primary key and the old one.

mbender SSCrazy Points: 2595 More actions · Answer 7

SeanLange

could i get an example of what you mean?

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Are the destination tables new ones? If they are my suggestion would work without the need for temp tables at all. If the destination tables already have data then it would certainly not. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbender SSCrazy Points: 2595 More actions · Answer 9

the new tables are existing tables, technically they are the same tables. I basically copying a record from itself and reinserting it into itself.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

That sounds like it is a bit odd. You are creating duplicate data of all three tables??? Is this a one time thing or something you need to do regularly?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbender SSCrazy Points: 2595 More actions · Answer 11

It would be an ongoing thing. Its for a CRM kind of project. The customer wants to create multiple versions so they can play with different options.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Could you use global temp tables or does the copy need to persist? I am sure there are some really slick ways to do this quickly sans cursor but like you I am stuggling to get past the first two tables. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbender SSCrazy Points: 2595 More actions · Answer 13

ya i was using temp tables to store the new primary keys. I did get it to work if I add a field to the table that I can store the previous primary key. That way when I insert the data to the temp table it will be the new primary key and the old one. Then I can use the temp table as part of the join on the next set of tables and insert the new primary key. It works I'm just wondering if there is a better way to do. The good thing about doing it this way is you can tell which fields are originals and which ones are copies because it will have that last field filled in if its a copy.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

So does this copy of the data need to stay around? Or are you using it for a little while and then dumping it. Is this like the user goes to a "let me speculate" area and you are going to generate all this temp data and when they leave it will go away again?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/