Insert between tables with identity

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
trojan_ukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
mallcoreCommented:
Something like this maybe?

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

and a where clause
0
trojan_ukAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mallcoreCommented:
Is AssID random or auto increment?
0
trojan_ukAuthor Commented:
in Table B Assid is an auto increment or Identity field, in Table C it is a FK
0
Aneesh RetnakaranDatabase AdministratorCommented:
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
mallcoreCommented:
And how are table b and table a connected?
0
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
can u run each statement one by one
0
trojan_ukAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trojan_ukAuthor Commented:
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
trojan_ukAuthor Commented:
Sorry yes I can, there is no select statement. I will check again
0
trojan_ukAuthor Commented:
aneeshattingal, thank you again for your time and help. Excellent job, everything works and I have learned form this. Many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.