?
Solved

Insert between tables with identity

Posted on 2009-07-03
22
Medium Priority
?
296 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

770 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