How to join more select and then to 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 2008 Forums

Transact-SQL (2008)

How to join more select and then to insert

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2013-09-26 : 16:11:49

Hi,

I want to select more and then to insert to other table:

Trigger:
Select Id,'AB' from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number IN (1,2,3)

Select Id,'AC' from
(Select row_number() over(order by id) as Number, Id from inserted) as b
Where a.Number IN (1,3)

How to insert like this:
Insert into tbl_BetSlipSystem(BetSlipEventId,SystemBet)
SystemBet=a,b

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-26 : 16:14:06

Not sure that I understand you, but here goes nothing:

Insert into tbl_BetSlipSystem(BetSlipEventId,SystemBet)
Select Id,'AB' from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number IN (1,2,3)
union all
Select Id,'AC' from
(Select row_number() over(order by id) as Number, Id from inserted) as b
Where a.Number IN (1,3)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

programer
Posting Yak Master

221 Posts

Posted - 2013-09-26 : 16:27:32

In this case I inserted 5 rows.

In my case I need to insert 1 rows.
Instead of insert 5 rows the same data to insert in 1 column

SystemBet:
1025,1027|1025,1029,etc...

I need id from the first table to insert in other table but in one column. No need more insert of this.

quote:
Originally posted by tkizer

Not sure that I understand you, but here goes nothing:

Insert into tbl_BetSlipSystem(BetSlipEventId,SystemBet)
Select Id,'AB' from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number IN (1,2,3)
union all
Select Id,'AC' from
(Select row_number() over(order by id) as Number, Id from inserted) as b
Where a.Number IN (1,3)

In this case I inserted 5 rows.

In my case I need to insert 1 rows.
Instead of insert 4 rows the same data to insert in 1 column

SystemBet:
1025,1027|1025,1029,etc...

I need id from the first table to insert in other table but in one column. No need more insert of this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-26 : 16:48:07

You'll need to provide a data example as your posts aren't clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

programer
Posting Yak Master

221 Posts

Posted - 2013-09-26 : 16:54:41

tbl_table1
Id, Event

tbl_table2
Id, EventId

In the first table I inserted event2,event3,event4,event5:

tbl_table1
Id, Event
25, event2
26, event3
27, event4
28, event5

In the second table I want to insert
tbl_table2
Id, EventId
13, 25,28

I used row_number()

Please help

quote:
Originally posted by tkizer

You'll need to provide a data example as your posts aren't clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

programer
Posting Yak Master

221 Posts

Posted - 2013-09-26 : 19:07:11

Still I need your help.

I don't know how to use multiple select statement with where

and results insert in the table.

Like
select id from table1 where id=1 I got result 1
select id from table1 where id=4 I got result 4
select id from table1 where id=12 I got result 12

In the second table I want to insert:
1,4,12

quote:
Originally posted by programer

tbl_table1
Id, Event

tbl_table2
Id, EventId

In the first table I inserted event2,event3,event4,event5:

tbl_table1
Id, Event
25, event2
26, event3
27, event4
28, event5

In the second table I want to insert
tbl_table2
Id, EventId
13, 25,28

I used row_number()

Please help

quote:
Originally posted by tkizer

You'll need to provide a data example as your posts aren't clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-26 : 21:03:56

You should not design a table like this. But if you must, you'll need to get a CSV function that will concatenate the values together.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

programer
Posting Yak Master

221 Posts

Posted - 2013-09-27 : 04:09:04

Hi,

I tried to use:

DECLARE @strValues varchar(8000)
SELECT @strValues = COALESCE(@strValues+',', '') + id
FROM
(
SELECT DISTINCT id
FROM dbo.tbl_BetSlipEvents
) X
ORDER BY id

SELECT [Result] = @strValues

but I got error:
Msg 208, Level 16, State 1, Line 13
Invalid object name 'dbo.tbl_BetSlipEvents'.

quote:
Originally posted by tkizer

You should not design a table like this. But if you must, you'll need to get a CSV function that will concatenate the values together.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-27 : 13:30:27

Perhaps you aren't in the right database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources