Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert between tables with identity

Posted on 2009-07-03
22
Medium Priority
?
301 Views
Last Modified: 2012-05-07
Hi,

What I am trying to do is insert around 2000 rows of data from Table A into Table B and Table C. Table C needs the Identity value from Table B and I can't figure out the best way to do this.

So some data from Table A is inserted into Table B, the identity field from Table B then needs to tied to the phone number in Table A and both need to be inserted into Table C


Table A

[PhoneNumber] <-- Number from here
[Surname]
[FirstName]
[PayrollNumber]
[Directorate]
[Division]
[CostCentre]
[EmpID]

Table B

[Status]
[TypeID]
[EmpID]
[CostID]
[Description]
[CompID]
[StartDate]
[EndDate]
[AdminID]
[Visible]
[tempid]
[AssID] <-- Identity (PK)


Table C
[MobDevID]
[TelNumber] <-- Number to here
[IntCode]
[AccNumber]
[Make]
[Model]
[SerNumber]
[AssID] <-- To here (FK)
[visible]
[MobDevID]

Hopefully this makes sense
0
Comment
Question by:trojan_uk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24771887
you need to list the columns you want to insert into:
INSERT INTO [Table C]
 ( TelNumber, ... )
 SELECT [PhoneNumber], ...
   FROM [Table A]

Open in new window

0
 
LVL 5

Expert Comment

by:mallcore
ID: 24771891
Something like this maybe?

select a.phonenumber, b.assid
into c.phonenumber, c.assid
from table a, table b

and a where clause
0
 

Author Comment

by:trojan_uk
ID: 24771963
Thanks for the replies, I'm not sure I explained myself to well.

Imagine table B and C have no data, I need to add a record to Table B  and then get the identity (Assid) of that record and assign it to a phonenumber from Table A and insert both phonenumber and AssID to Table C.

I can see where you are coming from, but that will only work if I have data in Table B, that's the problem, I have to add a record to Table B first, then somehow asisign it with a phonenumber in A, then insert both into C

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:mallcore
ID: 24772000
Is AssID random or auto increment?
0
 

Author Comment

by:trojan_uk
ID: 24772114
in Table B Assid is an auto increment or Identity field, in Table C it is a FK
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24772196
create table #temp (empID int , AssID int )
insert into B (EmpID)
OUTPUT Inserted.EmpID, Inserted.AssID INTO #temp
SELECT EmpID
FROM A

INSERT INTO C (TelNumber,AssID )
SELECT PhoneNumber, AssID
FROM A
INNER JOIN #temp t on a.empID = t.EmpID
0
 
LVL 5

Expert Comment

by:mallcore
ID: 24772201
And how are table b and table a connected?
0
 

Author Comment

by:trojan_uk
ID: 24772570
Thanks again,

aneeshattingal, I can see and understand your logic, however (my fault again) Table B is a one to many, so Table A may contain multiple phonenumbers to each employee, so the only unique column is the phonenumber field, but that gets inserted into Table C after Table B. So what I need is a link in a temp table which assigns an AssID to the phonenumber column and then do the final join as phonenumber = phonenumber not empid = empid

Also just wanted to point out that Table A is just a temp table and not part of the database structure. I have been given a CSV file from a client that I import into a SQL table, I then distribute the data amongst the various tables.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24773335
can u give me an example, just with minimum values (empID & phone), i thought my query will do it by adding a DISTINCT EMpID in the first statement
0
 

Author Comment

by:trojan_uk
ID: 24773456
OK, So Table A would be like: (col 1 = phonenumber, col 7 = costid, col 8 = empid)

07891242975|Fred|Bloggs|00000000|City Development|Highways and Transportation|27152|3435|7276
07891243024|Fred|Bloggs|00000000|City Development|Highways and Transportation|27152|3435|7276
07891243035|Fred|Bloggs|00000000|City Development|Highways and Transportation|27152|3435|7276

This shows a user with three numbers (empid in last column). So in this example I would want to add three entries into Table B, get the identities back (AssID) tie them into a number then enter into Table C. So Table B and C would look like:

Table B (1st value = empid, 2nd value = costid, 3rd value identity (assid))

--|--|7276|3435|--|--|--|--|--|--|--|1
--|--|7276|3435|--|--|--|--|--|--|--|2
--|--|7276|3435|--|--|--|--|--|--|--|3


Table C (1st value = phonenumber, 2nd value = assid from table B,)

07891242975|--|--|--|--|--|--|1|id
07891243024|--|--|--|--|--|--|2|id
07891243035|--|--|--|--|--|--|3|id

I have used '--' to indicate values that will be generic.

Hope this helps
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24773726
an indirect option

ALTER TABLE B ADD PhoneNumber varchar(15)   --- this will be deleted later

create table #temp (PhoneNumber varchar(15)   , AssID int )
insert into B (EmpID)
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #temp
SELECT EmpID
FROM A

INSERT INTO C (TelNumber,AssID )
SELECT PhoneNumber, AssID
FROM A
INNER JOIN #temp t on a.PhoneNumber = t.PhoneNumber

ALTER TABLE B DROP  COLUMN PhoneNumber
0
 

Author Comment

by:trojan_uk
ID: 24773792
Thank you.

I have added some other fields that would be generic and the CostID field, but I have obviously got the syntax wrong, it says incorrect syntax near EmpID. Can you tell me what I am doing wrong? The changes are to the inserts


ALTER TABLE wbAssetTrack ADD PhoneNumber varchar(15)   --- this will be deleted later

CREATE TABLE #Temp (PhoneNumber varchar(15)   , AssID int )
INSERT INTO wbAssetTrack ('1',23,EmpID,CostID,'',24,'1 jan 2009','1 jan 9999','','1','')
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #Temp
SELECT EmpID,CostID
FROM UserData

INSERT INTO wbMCD (TelNumber,'','','','','',AssID,'1' )
SELECT PhoneNumber, AssID
FROM UserData
INNER JOIN #Temp t on UserData.PhoneNumber = t.PhoneNumber

ALTER TABLE wbAssetTrack DROP  COLUMN PhoneNumber
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24773862
ALTER TABLE wbAssetTrack ADD PhoneNumber varchar(15)   --- this will be deleted later

CREATE TABLE #Temp (PhoneNumber varchar(15)   , AssID int )
INSERT INTO wbAssetTrack ()
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #Temp
SELECT '1',23,EmpID,CostID,'',24,'1 jan 2009','1 jan 9999','','1',''
FROM UserData

INSERT INTO wbMCD ( )
SELECT PhoneNumber,'','','','','',AssID,'1'
FROM UserData
INNER JOIN #Temp t on UserData.PhoneNumber = t.PhoneNumber

ALTER TABLE wbAssetTrack DROP  COLUMN PhoneNumber
0
 

Author Comment

by:trojan_uk
ID: 24773988
aneeshattingal, thank you for your time an patience on this.

I assumed that I would need to add the PhoneNumber to the column list, however I get the error "Invalid column name 'PhoneNumber'"


ALTER TABLE wbAssetTrack ADD PhoneNumber varchar(15)

CREATE TABLE #Temp (PhoneNumber varchar(15)   , AssID int )
INSERT INTO wbAssetTrack ([Status],[TypeID],[EmpID],[CostID],[Description],[CompID],[StartDate],[EndDate],[AdminID],[Visible],[tempid],[AssID],[PhoneNumber])
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #Temp
SELECT '1',23,EmpID,CostID,'',24,'1 jan 2009','1 jan 9999','','1','',NULL,PhoneNumber
FROM UserData

INSERT INTO wbMCD ([TelNumber],[IntCode],[AccNumber],[Make],[Model],[SerNumber],[AssID],[visible])
SELECT PhoneNumber,'','','','','',AssID,'1'
FROM UserData
INNER JOIN #Temp t on UserData.PhoneNumber = t.PhoneNumber

ALTER TABLE wbAssetTrack DROP  COLUMN PhoneNumber
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24774163
can u check which line you are getting that error messge

also change the last insert statement to

INSERT INTO wbMCD ([TelNumber],[IntCode],[AccNumber],[Make],[Model],[SerNumber],[AssID],[visible])
SELECT t.PhoneNumber,'','','','','',AssID,'1'
FROM UserData
INNER JOIN #Temp t on UserData.PhoneNumber = t.PhoneNumber
0
 

Author Comment

by:trojan_uk
ID: 24774183
Msg 207, Level 16, State 1, Line 4
Invalid column name 'PhoneNumber'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'PhoneNumber'.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24774204
can u run each statement one by one
0
 

Author Comment

by:trojan_uk
ID: 24774236
Not sure if I gave done what you asked correctly, I ran this:

ALTER TABLE wbAssetTrack ADD PhoneNumber varchar(15)

CREATE TABLE #Temp (PhoneNumber varchar(15)   , AssID int )
INSERT INTO wbAssetTrack ([Status],[TypeID],[EmpID],[CostID],[Description],[CompID],[StartDate],[EndDate],[AdminID],[Visible],[tempid],[AssID],[PhoneNumber])
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #Temp

SELECT '1',23,EmpID,CostID,'',24,'1 jan 2009','1 jan 9999','','1','',NULL,PhoneNumber
FROM UserData

ALTER TABLE wbAssetTrack DROP  COLUMN PhoneNumber

and got the same:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'PhoneNumber'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'PhoneNumber'.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24774387
ALTER TABLE wbAssetTrack ADD PhoneNumber varchar(15)
go
if object_ID('tempdb..#temp' ) IS NOT NULL
    DROP TABLE #Temp
CREATE TABLE #Temp (PhoneNumber varchar(15)   , AssID int )
INSERT INTO wbAssetTrack ([Status],[TypeID],[EmpID],[CostID],[Description],[CompID],[StartDate],[EndDate],[AdminID],[Visible],[tempid],[AssID],[PhoneNumber])
OUTPUT Inserted.PhoneNumber , Inserted.AssID INTO #Temp

INSERT INTO wbMCD ([TelNumber],[IntCode],[AccNumber],[Make],[Model],[SerNumber],[AssID],[visible])
SELECT t.PhoneNumber,'','','','','',AssID,'1'
FROM UserData
INNER JOIN #Temp t on UserData.PhoneNumber = t.PhoneNumber
0
 

Author Comment

by:trojan_uk
ID: 24774421
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'INSERT'.

But I can't see a problem with the insert statement?
0
 

Author Comment

by:trojan_uk
ID: 24774430
Sorry yes I can, there is no select statement. I will check again
0
 

Author Closing Comment

by:trojan_uk
ID: 31599536
aneeshattingal, thank you again for your time and help. Excellent job, everything works and I have learned form this. Many thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question