Insert the row to the table but one column

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 2005 Forums

Transact-SQL (2005)

Insert the row to the table but one column

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-05 : 12:44:18

How to insert the row to the table but one column form another table

I have table name:

NAME, IDENTIFY, Term

Another table: identify

12
34
56
66
11

Insert into NAME(name, identify, term )
values('ABC', identify, 12)

but identify values is from table identify. Is there any way to accomplish it?

Thank you very much!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 12:53:45

How will you know which values go with which rows in Identify?

Insert into NAME(name, identify, term )
select 'ABC', identify, 12
from identify
where...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-05 : 15:01:25

Tara;

Thanks for the response. I just need to insert same name and term: 'ABC',12 with different identify

12
34
56
66
11

Something like this:

Insert into NAME(name, identify, term )
values('ABC', '12', 12)

Insert into NAME(name, identify, term )
values('ABC', '34', 12)

Insert into NAME(name, identify, term )
values('ABC', '56', 12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 15:12:59

See my example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-05 : 15:53:53

Tara:

Thanks. I use the query you provide:
Insert into NAME(name, identify, term )
select 'ABC', identify, 12
from identify

There is error: Msg 128, Level 15, State 1, Line 2
The name "identify" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

because identify is column name. How to fix it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 16:25:19

It works fine for me. Here is my example:


create table identify (identify smallint)
create table [name]([name] varchar(5), identify smallint, term smallint)

insert into identify values(5)
insert into identify values(6)
insert into identify values(12)

Insert into NAME(name, identify, term )
select 'ABC', identify, 12
from identify

select * from identify

drop table [name], identify

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-05 : 16:48:07

Tara:

Thank you very much! I made a mistake and have typo. You are super!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 16:55:17

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources