making a select as a part of an insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

making a select as a part of an insert

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-15 : 06:30:53

when i make an insert on some fields
can i on 1 of the fiels in the values make a select which will bring a value from another table?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 06:40:25

Hi,
yes you can do that..
like this...
insert into <table1> values(<col1>,<col2>,<col3>)
select @fiel1,@field2,<columnname>
from <table2>

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 06:41:29

oops
insert into <table1> ~~values~~(<col1>,<col2>,<col3>)
select @fiel1,@field2,<columnname>
from <table2>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 06:44:30

Also in SQL Server help file, read about insert into

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-15 : 15:30:32

sorry i meant something more like
insert into table1 values(@aaa,@bbb,(select id from tableXXX where id=1))

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 15:38:41

insert into table1 (Column1, Column2, Column3)
SELECT @aaa,@bbb, id
from tableXXX
where id=1

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 05:54:56

or if the row may not exist, and you want a NULL inserted instead, then

insert into table1 (Column1, Column2, Column3)
SELECT @aaa,@bbb, (SELECT MAX(id) from tableXXX where id=1)

EDIT: Added MAX() to prevent multiple rows
Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 06:36:32

quote:
Originally posted by Kristen

or if the row may not exist, and you want a NULL inserted instead, then

insert into table1 (Column1, Column2, Column3)
SELECT @aaa,@bbb, (SELECT id from tableXXX where id=1)

Kristen

That will lead to error if subquery returns more than one value.


If exists(SELECT id from tableXXX where id=1)	
	insert into table1 (Column1, Column2, Column3)
	SELECT @aaa,@bbb, id from tableXXX where id=1
else
	insert into table1 (Column1, Column2, Column3)
	SELECT @aaa,@bbb, (SELECT id from tableXXX where id=1)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 07:09:59

"That will lead to error if subquery returns more than one value"

Yup, I glossed over that thinking it was becoming complicated enough already!

In such circumstances I would use MAX or MIN to ensure I got a single value, or a SELECT TOP 1 with an ORDER BY of the PK columns of the table, to ensure it was repeatable - but there is still the risk that the PKs change and the statement gets overlooked by "maintenance"!

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-16 : 08:23:16

madhivanan
why not from the begingto do just :

SELECT @aaa,@bbb, (SELECT id from tableXXX where id=1)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 08:37:57

Read previous replies

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 10:02:31

Can you guarantee that

(SELECT id from tableXXX where id=1)

will select only one row?

Even if you make a typing mistake in it, by error

?

For every DDL change that you might make to that table in the future?

Even if there could only possibly be one row now it might be wise to put a MAX() in there to safeguard the future.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 12:38:18

Mine won't fail if there is more than one row.

pelegk2, did you try this one?

insert into table1 (Column1, Column2, Column3)
SELECT @aaa,@bbb, id
from tableXXX
where id=1

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 12:57:13

"Mine won't fail if there is more than one row"

No ... but it will insert multiple rows

which I took the original example to indicate wasn't the desired outcome:
"i meant something more like
insert into table1 values(@aaa,@bbb,(select id from tableXXX where id=1))"
but that's open to interpretation of course!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 13:05:03

Ah yes.

Re-reading the post...select id from tableXXX where id=1...well id equals 1, so 1 will be returned from the select. So then this would be fastest:

insert into table1 (Column1, Column2, Column3)
VALUES(@aaa,@bbb, 1)

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 13:19:01

Champion!

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-26 : 03:01:02

1 moer question - the same on update
i tried to do
update tbl1 set xxx=(select field1 from table2 where id=55)
where id=11
and it dosent wokr any idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-26 : 11:15:59

may be
select field1 from table2 where id=55
returning more than 1 field1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-26 : 21:01:25

pelegk2,

What is the error that you are getting on your update statement?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources