how to split the insert to table - depend the number
-
help
the problem is on the " trigger " only
this is the error
------------------
Msg 156, Level 15, State 1, Procedure Incorrect syntax near the keyword 'with'
----------------------
my question is what is on " X " ?????
this line
"with
x(n) as (select 1 union all select 1 + n from x where n < 100)"
do i must to chang any value on X to my table
like this X.TB_PARTS
maybe this is my problem
-----------------------
i have tow table
1 ) TB_PARTS
2) NEW_TB
select from TB_PARTS
insert into TB_NEW
-
The trigger is for the "TB_New" table. It will fire when ever an insert occurs to your "new table". By doing it this way you don't have to worry about outside applications failing to follow the business rules. It happens no matter what.
You do not have to do anything but implement the trigger as written. However the error you are getting indicates you are NOT on SQL 2005. If you are on SQL 2005 something else very strange is going on.
However if you were/are on SQL 2005 and you applied the TRIGGER code correctly then what happens is the trigger uses the "with x(n)..." as a table based function which creates an in memory table with 100 sequentially numbered rows in it which is then joined to the "inserted" table to replicate the row being inserted a number of times equal to the value of new_number.
Here is equivalent code that will work in SQL 2000, please try the following:
--dump any old table/data
if object_id('tb_parts') is not null drop table tb_parts
go
if object_id('new_tb') is not null drop table new_tb
go
--Create test tables
create table tb_parts (sn varchar(20),fld1 char(5), fld2 char(5), fld3 char(5), fld4 char(5))
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
--add a trigger to the "new parts table" that will replicate the data to be inserted
--so that the total number of rows inserted is equal to the value of "new_number"
Create trigger tr_new_tb_i on new_TB for Insert as
begin
--create a temporary table with 100 sequential numbers in it
--increase this value if you will ever need more than 100 replicated rows.
SELECT TOP 100
n = IDENTITY(INT,1,1)
INTO #x
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--Insert into the new table the results of joining
--the "inserted" table records with our temporary table
--which gives us a subset of a cross join
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)
--clean up
drop table #x
end
go
--create test data for the tb_parts table
insert into tb_parts
select '1234','1','2','3','4' union all
select '2345','2','3','4','5' union all
select '3456','3','4','5','6' union all
select '4567','4','5','6','7'
go
--use the test data int the tb_parts table
--to create the data in our "new" table which has
--our fancy new trigger and will result in multiple rows being
--inserted for each row in the tb_parts table.
--This insert will create 20 rows for sn=1234
insert into new_tb
select 20,getdate(),user, *
from tb_parts
where sn = '1234'
go
--this insert will create 5 rows for each sn (2345 and 4567)
insert into new_tb
select 5,getdate(),user, *
from tb_parts
where sn = '2345' or sn = '4567'
go
--this insert will create 15 rows for the sn=3456
insert into new_tb
select 15,getdate(),user, *
from tb_parts
where sn = '3456'
go
--Display proof we got what we wanted.
select * from new_tb order by new_number
--James
-
Midan,
At the risk of sounding mean, you're starting to sound a bit like a broken record... answer Jame's question... ARE YOU USING SQL SERVER 2005 OR NOT?
--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 -
Thanks Jeff, this is getting old. And thanks for the code that generates the 100 sequential numbers. I got that off a post of yours a few weeks back where you generated a whole bunch of "test" data. Love that code snippet and I've used it many places since. (I noticed in my last post that it still has your original comment about the CROSS JOIN)
James.
-
Heh... thanks, James. Yeah, I use it a lot, as well... there are a couple of methods that are a bit faster, but there's none simpler or easier to write. Really appreciate the feedback.
--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 am use sql server 2005 sp2
-
Well I don't suppose it matters at this point. Please try the last code batch I posted. It does not rely on the advanced features of 2005 and should run in any version you are actually running against.
Just as an aside, if you still get an error message please post the results of the following SQL statements (run them in the SAME session that produces any error messages)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
go
SELECT @@Version
go
Not that I don't believe you or anything (I believe you believe, but am less confident of the reality of that belief)
James.
-
it working !!
thanks to you all
my problem happen because of old sql server 2000 residue
i remove all the old sql server 2000
i restart my computer and it working
tnx for all the people here
-
my last question
how can i insert multy insert ?
like this
select 15,getdate(),user, *
from tb_parts
where sn = '3456,888,9999,8765,55,56,57'
TNX
-
My last post had one method of doing it:
--this insert will create 5 rows for each sn (2345 and 4567)
insert into new_tb
select 5,getdate(),user, *
from tb_parts
where sn = '2345' or sn = '4567'
go
another is:
--this insert will create 5 rows for each sn (2345 and 4567)
insert into new_tb
select 5,getdate(),user, *
from tb_parts
where sn in ('2345','4567')
go
Use the second method when you have 3 or more items (I don't think it's anymore efficient but it is easier to write).
James.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply