Solved

Insert between tables with identity

Posted on 2009-07-03
22
241 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
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:mallcore
Comment Utility
Is AssID random or auto increment?
0
 

Author Comment

by:trojan_uk
Comment Utility
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
Comment Utility
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
Comment Utility
And how are table b and table a connected?
0
 

Author Comment

by:trojan_uk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:trojan_uk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
can u run each statement one by one
0
 

Author Comment

by:trojan_uk
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry yes I can, there is no select statement. I will check again
0
 

Author Closing Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now