how to split the insert to table - depend the number
-
"how to split number to the insert table - depend the number"
how to do this ?
if i insert to my table
the number=20
i get 20 rows (on my table)
if i insert to table
the number=5
i get 5 rows (on my table)
like this
and after this insert
i get 15 rows
HOW
tnx
-
Im not somewhere I can test this, but this might be a place to start.
create proc num_insert (@rows_in int) as
create table #testt ( somecolumn varchar(20) NOT NULL,
someothercolumn int NOT NULL)
declare @cntr int
set @cntr = 0
while @cntr < @rowsin
BEGIN
insert #testt (somecolumn, someothercolumn) values ('a',1)
set @cntr = @cntr + 1
END
GO
exec num_insert 20
go
select * from #testt
go
exec num_insert 5
go
select * from #testt
go
You could add any number of things to the proc to randomize the data, but I'm not sure this is really what you're asking for. What are you trying to accomplish with this?
And then again, I might be wrong ...
David Webb -
Interesting problem. However you didn't say what the table looks like that you are inserting into, but assuming it has a single integer column the following will do what you want with a TRIGGER on the table:
if object_id('mytable') is not null drop table mytable
go
create table mytable (col1 int)
go
Create trigger tr_mytable_i on mytable for Insert as
begin
with x(n) as (select 1 union all select 1 + n from x where n < 100)
insert into mytable (col1)
select n
from x join inserted i on (x.n < i.col1)
end
go
insert into mytable
select 20 union all
select 5 union all
select 15
go
select * from mytable
--The result is 40 rows (20 rows + 5 rows + 15 rows). The key is the ability to use a recursive common table expression along with the insert command. Wasn't sure it could be done and was suprised when it actually worked (learn something new every day). I think this might have some potential in other areas.
--NOTE: I limited the CTE to creating numbers between 1 and 100 but you could set it as high as the top value of integer, though I don't know the impact of that on performance.
--James.
-
all the examples above are with "Incorrect syntax"but my problem is how can i do it
like this
select 1 item from my TB_PARTS
(TB_PARTS field s=sn,fld1,fld2,fld3,fld4)
and insert to my NEW_TB
(NEW_TB field s=new_number,new_date,user,sn,fld1,fld2,fld3,fld4)
and i get 20 rows of this item
X=20
TNX
ILAN
-
So, you want 20 identical rows? And the "SN" doesn't change?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
yes i need to
select from TB_PARTS field s=sn,fld1,fld2,fld3,fld4
and insert to NEW_TB field s=new_number,new_date,user,sn,fld1,fld2,fld3,fld4
and get in table "NEW_TB" 20 rows
to "Jeff Moden " the SN is the id of this Part
TNX
-
Yep... I know that... you're saying you want 20 new rows with the same SN?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
someone can halp
TNX
-
Midan,
Answer the question and I can help... do you want the SN to be the same for all 20 new rows? This is an easy problem... I just need the answer to THAT question.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Midan: I beg your pardon, but I don't think my syntax was incorrect, I think your question was lacking in information. My solution of adding to the trigger of the "New_TB" should work just fine if you simply add the other columns you need to the insert statement in the trigger.
For example:
Create trigger tr_new_tb_i on new_TB for Insert as
begin
with x(n) as (select 1 union all select 1 + n from x where n < 100)
insert into new_tb (new_number,new_date,,sn,fld1,fld2,fld3,fld4)
select i.new_number,i.new_date,i.,i.sn,i.fld1, i.fld2, i.fld3, i.fld4
from x join inserted i on (x.n < i.new_number)
end
go
The entire TESTABLE solution is:
if object_id('new_tb') is not null drop table new_tb
go
create table new_tb (new_number int, new_date datetime, varchar(20), sn varchar(20),fld1 char(5), fld2 char(5), fld3 char(5), fld4 char(5))
go
Create trigger tr_new_tb_i on new_TB for Insert as
begin
with x(n) as (select 1 union all select 1 + n from x where n < 100)
insert into new_tb (new_number,new_date,,sn,fld1,fld2,fld3,fld4)
select i.new_number,i.new_date,i.,i.sn,i.fld1, i.fld2, i.fld3, i.fld4
from x join inserted i on (x.n < i.new_number)
end
go
insert into new_tb
select 20,getdate(),user,'12345','1','2','3','4' union all
select 5,getdate(),user,'12345','1','2','3','4' union all
select 15,getdate(),user,'12345','1','2','3','4'
go
select * from new_tb order by new_number
-
HI TNX
i have problem winth sql server 2005 Incorrect syntax
1
----------------
Create
trigger tr_new_tb_i on new_TB for Insert as
begin
with x(n) as (select 1 union all select 1 + n from x where n < 100)
insert into new_tb (new_number,new_date,,sn,fld1,fld2,fld3,fld4)
select i.new_number,i.new_date,i.,i.sn,i.fld1, i.fld2, i.fld3, i.fld4
from x join inserted i on (x.n < i.new_number)
-------------------------------------------------------------
the error i get
Msg 156, Level 15, State 1, Procedure tr_new_tb_i, Line 3
Incorrect syntax near the keyword 'with'.
----------------------------------------------------------------
TNX
-
Are you sure your runing SQL 2005? I tested that entire script on my development copy of SQL 2005 and it runs as expected without errors.
When I change the connection to SQL 2000 instance and rerun the script I then get the error you are reporting.
My solution will only work in SQL 2005, which is the first version to allow Common Table Expressions.
James.
-
Try this:
insert into NEW_TB field
select
new_number=1,
new_date=getdate(),
user='midan',
sn,
fld1,
fld2,
fld3,
fld4
from TB_PARTS;
go 20
-
TNX
ineed help
the problem with my sql server 2005 SP2 is on this
the error i get i whan i run the "trigger tr_new_tb_i "
Msg 156, Level 15, State 1, Procedure tr_new_tb_i, Line 3
Incorrect syntax near the keyword 'with'
--------------
Create trigger tr_new_tb_i on new_TB for Insert as
begin
with x(n) as (select 1 union all select 1 + n from x where n < 100)
insert into new_tb (new_number,new_date,,sn,fld1,fld2,fld3,fld4)
select i.new_number,i.new_date,i.,i.sn,i.fld1, i.fld2, i.fld3, i.fld4
from x join inserted i on (x.n < i.new_number)
-----------------------------------------
-
Sorry dude, don't know why your are getting the syntax error. The script should work.
I only get that error when I try to run the script on a MSSS 2000 instance.
You tried running the entire script right? That means the Create Table portion and everything. And you are definately connected to a MSSS 2005 instance?
If all that is true, I'm out of ideas.
James.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply