More insert problems. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

More insert problems.

Author

Topic

BBrian
Starting Member

11 Posts

Posted - 2005-08-12 : 09:27:20

I'm trying to take 3 identity fields (ci_number) from my main table and put them into a table called interfaces. I think it's kinda clear from the query what I'm trying to do. The tables are below. The first is where I'm taking data from and the second is what I want the result to be like.

insert into interfaces (ci_number, fromapp, toapp) select ci_number, ci_number, ci_number from main where ci_name = 'Swift Alliance to intelliMATCH','Swift Alliance','intelliMATCH'

Main
ci_number ci_name
6 Swift Alliance
11 Intellimatch
54 Swift Alliance to intelliMATCH

Interfaces
ci_number fromapp toapp
54 6 11

Brian!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-12 : 09:34:45

Replace the where clause with:

where ci_name IN ('Swift Alliance to intelliMATCH','Swift Alliance','intelliMATCH')

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 09:38:10

I think this is cross tab
Search for cross tab in the articles

Madhivanan

Failing to plan is Planning to fail

BBrian
Starting Member

11 Posts

Posted - 2005-08-12 : 09:39:52

That gave me

Interfaces

ci_number fromapp toapp
6 6 6
54

BBrian
Starting Member

11 Posts

Posted - 2005-08-12 : 10:20:31

Would using crosstab not be complicating it (read: cross tab looks to be very compliacted)

I have to insert 97 rows like this. The must be some simple statement to do this!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 11:01:54

Why do you want to insert in a seperate column in interfaces table?
Do you want it to show this in your presentation layer like this?


Declare @t table(ci_number int , ci_name varchar(50))
Insert into @t values(6 ,'Swift Alliance')
Insert into @t values(11, 'Intellimatch')
Insert into @t values(54, 'Swift Alliance to intelliMATCH')
declare @s varchar(2000)
set @s=''
Select @s=Isnull(@s,'')+' ' +cast(ci_number as varchar) from @t
select @s

Madhivanan

Failing to plan is Planning to fail

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-12 : 11:42:40

How about something like this:


select 
	main.ci_number, 
	main_from.ci_number as fromapp, 
	main_to.ci_number as toapp
from 
	dbo.main as main
	join dbo.main as main_from
		on charindex(main_from.ci_name, main.ci_name) = 1
			and charindex(' to ', main_from.ci_name)  = 0
		join dbo.main as main_to
			on charindex(main_to.ci_name, main.ci_name) > 1
				and charindex(' to ', main_to.ci_name) = 0
where 
	charindex(' to ', main.ci_name) > 0

Mark

anitha
Starting Member

2 Posts

Posted - 2005-08-12 : 17:45:29

insert into interfaces
(select a.ci_number,
( select b.ci_number from main b where b.ci_name= 'Swift Alliance to intelliMATCH'),
(select c.ci_number from main c where c.ci_name = 'Swift Alliance'),
from main a where a.ci_name = 'intelliMATCH')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources