Dynamic Inserts using union all

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)

Dynamic Inserts using union all

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-17 : 08:36:44

Hello Guys,
whats the best way to implement this:

I have a table that values will be inserted based on a dynamic value, ie if its 100, then 100 will be selected from the parent table, and if its 10,000 then 10,000 will be selected using the union all clause.

Problem, is that its a mixture of variables and table values. Here is my code below



declare @y int, @x int
set @y = 100


INSERT myhistory (ID, recipient, Username 
 select 
while @x <= @y
(@senderID, (select recipient from log_history_table where MyID = @ID) ,@user 

SET @X = @X + 1

Am a bit confused :-(

SUMMARY: I want to run a loop or set based approach to get the total values selected into the main table from the log table, alongside with the variables

MM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 08:59:13

Not sure what you want

declare @y int, @sql varchar(100)
set @y = 100

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table, master..spt_values
WHERE MyID = @ID and type='p' and number<@y

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:41:47

[code]INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

GO 100
[/code]

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-17 : 13:49:08

quote:

Originally posted by visakh16

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

GO 100

declare @counter int

set @counter = select count(*) from myhistory where myid = @id

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

GO @counter

But if you have multiple values of myid, then it will run that number of times, times the count value.

Alternatively, you can omit the go

Hence

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

Cause it will loop already.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-18 : 04:11:23

quote:
Originally posted by afrika

quote:
Originally posted by visakh16

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

GO 100

declare @counter int

set @counter = select count(*) from myhistory where myid = @id

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

GO @counter

But if you have multiple values of myid, then it will run that number of times, times the count value.

Alternatively, you can omit the go

Hence

INSERT myhistory (ID, recipient, Username)
SELECT @senderID, recipient, @user from log_history_table WHERE MyID = @ID

Cause it will loop already.

GO @counter wont work. You need dynamic sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources