how to write a insert query for this?

Question

Post reply

how to write a insert query for this?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
March 8, 2012 at 12:00 pm

#254378

here iam having two table in one table the values are placed pre defined depend on that i want to insert datas for the second table
table 1 (this is the predefined table)
contacttypeid (pk) contactvalue
1 prmaryperson
2 secondary person
3 phoneno 1
4 phone2
5 mailid1
6 mailid 2
the second table was like this
account id(pk) contacttypeid(fk) datas (as varchar (50) datatype)
and now i need output like this
account id(pk) contacttypeid(fk) datas
1 1 bala
2 2 siva
3 3 654321
4 4 543216
5 5 era@gmil.com
6 6 sgrewrt@yh.com
here pk represent primary key
and fk represent foregin key

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

This makes no sense right now. Please provide sample data for the second table.

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 2

i show u the output how it should come

accontid contacttype data

1 1 bala

similarly i mentioned the output in my first post itself

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

where does "bala" in the datas column originate?

likewise all the other details

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jared SSC Guru Points: 61793 More actions · Answer 4

er.sivaganesh (3/8/2012)
i show u the output how it should come
accontid contacttype data
1 1 bala
similarly i mentioned the output in my first post itself

I cannot get the expected results with only 1 row in the second table. You need to provide data for both tables that is needed to give the expected results.

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 5

bala is the sample insert data for table 2 depend upon contact type 1

Jared SSC Guru Points: 61793 More actions · Answer 6

er.sivaganesh (3/8/2012)
bala is the sample insert data for table 2 depend upon contact type 1

Ok, given what you have given me I guessed at sample data.

USE tempdb

CREATE TABLE #contacts (contacttypeid int not null, contactvalue varchar(100))

INSERT INTO #contacts

SELECT 1, 'primaryperson'

UNION ALL

SELECT 2, 'secondaryperson'

UNION ALL

SELECT 3, 'phoneno1'

UNION ALL

SELECT 4, 'phone2'

UNION ALL

SELECT 5, 'mailid1'

UNION ALL

SELECT 6, 'mailid2'

CREATE TABLE #accounts (accountid int not null, contacttypeid int, datas varchar(50))

INSERT INTO #accounts

SELECT 1, 1, 'bala'

UNION ALL

SELECT 2, 2, 'siva'

UNION ALL

SELECT 3, 3, '654321'

UNION ALL

SELECT 4, 4, '543216'

UNION ALL

SELECT 5, 5, 'era@gmil.com'

UNION ALL

SELECT 6, 6, 'sgrewrt@yh.com'

Since you seem to not want to do any work to help us to help you, I have come up with the best solution to give you the desired results:

SELECT *

FROM #accounts

If all of the data you need is in table 2, ignore table 1 and simply select from table 2.

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 7

here iam having two table in one table the values are placed pre defined depend on that i want to insert datas for the second table

table 1 (this is the predefined table this table always wil have same data 1for primaryperson)

contacttypeid (pk) contactvalue

1 prmaryperson

2 secondary person

3 phoneno 1

4 phone2

5 mailid1

6 mailid 2

and now i need output like this for the second table depend upon contacttypeid of first table

and here bala is the prmaryperson and siva is the secondary person depend upon contact type id i just

want to write insert query for the second table only

account id(pk) contacttypeid(fk) datas

1 1 bala

2 2 siva

3 3 654321

4 4 543216

5 5 era@gmil.com

6 6 sgrewrt@yh.com

here pk represent primary key

and fk represent foregin key

Jared SSC Guru Points: 61793 More actions · Answer 8

Like this?

INSERT INTO table2

SELECT contacttypeid, contacttypeid, datas

FROM table1

OR

CREATE TABLE table2 (accountid int identity(1,1), contacttypeid int, datas varchar(50))

INSERT INTO table2 (contacttypeid, datas)

SELECT contacttypeid, datas

FROM table1

ORDER BY contacttypeid

Jared
CE - Microsoft

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 9

from table one we are getting contacttype int value only

Jared SSC Guru Points: 61793 More actions · Answer 10

er.sivaganesh (3/8/2012)
from table one we are getting contacttype int value only

Until you give me better data and a better sample, I can not help you. Right now, your expected results in table 2 have the exact same values in column 1 and column 2. So just insert the same value in each.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

er.sivaganesh (3/8/2012)
here iam having two table in one table the values are placed pre defined depend on that i want to insert datas for the second table
table 1 (this is the predefined table)
contacttypeid (pk) contactvalue
1 prmaryperson
2 secondary person
3 phoneno 1
4 phone2
5 mailid1
6 mailid 2
the second table was like this
account id(pk) contacttypeid(fk) datas (as varchar (50) datatype)
and now i need output like this
account id(pk) contacttypeid(fk) datas
1 1 bala
2 2 siva
3 3 654321
4 4 543216
5 5 era@gmil.com
6 6 sgrewrt@yh.com
here pk represent primary key
and fk represent foregin key

Please read the first article I reference below in my signature block. It will walk you through the steps you need to provide us with the information we need to help you.