November 7, 2008 at 8:52 am
Hi there,
I like to keep several versions of a record in a table. To accomplish that I use the following SQL statement
BEGIN
IF object_id('tempdb..#T') IS NOT NULL
DROP TABLE #T
SELECT * INTO #T FROM deleted
UPDATE #T SET ID=NewID();
INSERT dbo.tblAgencies SELECT * FROM #T --(1)--
UPDATE dbo.tblAgencies
SET[Modified] = GetDate(),
[UserName] = suser_sname(),
[RID] = newid(),
[Version] = ((Select [Version] From inserted) + 1)
Where [ID] In (Select [ID] From inserted)
DROP TABLE #T
END
Now The statement fails on the INSERT by (1) the error is "Insert Error: Column name or number of supplied values does not match table definition."
Well i can imagine that as the tables are not exactly the same. Eg missing relation and or it is the calculated column which is a nvarchar in the temptable. A solution is to write out all the fields I like to copy (Always all) But that will be a bitch to maintain :ermm:
So is there a beter solution maybe. I was thinking to modify directly on the deleted table but SQL is not allowing that.
Maybe something on the fly while inserting.
Thx.
November 7, 2008 at 9:01 am
I assume this code is part of a trigger? Are you sure there are records in the deleted virtual table?
November 7, 2008 at 9:13 am
Yep, why ?
[font="Courier New"][/font]
ID RID DID Version Created Modified UserName IsDeleted Company Street StreetNumber City_ID StateProvince ZipPostalCode AddressHash PoBox PoCity Country_ID PhoneNumber FaxNumber EMail WebSite Telex Note
------------------------------------ ------------------------------------ ------------------------------------ ----------- ----------------------- ----------------------- ---------------------------------------------------------------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------------------------ ---------------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
43295861-B6CD-4E97-922F-40D3683926CD 64514801-5974-41D4-884D-95B85A6AF27F 5BCF250F-1495-4353-A8F6-B6314924C175 0 2008-11-06 17:06:19.880 2008-11-06 17:06:19.880 DOMAIN\Joe 0 1a NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(1 row(s) affected)
November 7, 2008 at 10:45 am
You can't insert values into calculated columns. The answer is to list all the columns.
Also, you are looking for trouble by doing a
SELECT ...
INTO ...
FROM ...
See the following articles.
http://www.sqlservercentral.com/articles/Performance+Tuning/temptables/148/
http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 8, 2008 at 3:36 am
Hi,
Thx for the reply. But I do not want to modify the calculated column.
The thing is that the insert fails because the signature of the temp table is not the same as the destination table. and the bottlenecks here are or the calculated column or the relation at the destination table.
Of course i can write it out with all the columns. that will be working. But that will be a maintenance issue. Its easy enough to forget to update
those trigger when updating a table definition.
So if possible I looking for some generic way to this. I need to copy the complete row always for this.
November 10, 2008 at 10:46 am
rd (11/7/2008)
SELECT * INTO #T FROM deleted
UPDATE #T SET ID=NewID();
INSERT dbo.tblAgencies SELECT * FROM #T --(1)--
Two questions:
(1) Does the deleted table contain rows which have been deleted from dbo.tblAgencies?
(2) Does dbo.tblAgencies contain one or more computed columns?
If so, whether you intend to or not, your query is pushing data at a computed column. You will find that the number of columns returned by a SELECT * is not the same as the number of columns required for an INSERT. You can check this in SQL Server Mgmt Studio by right clicking on the table and letting it generate a SELECT for you, then doing the same thing and generating an INSERT. You will find that computed columns will not accept values.
See the example below. The only difference between in the computeTbl and the deletions table is that the deletions table does NOT have a computed column. The trigger shows that the SELECT * insertion you want to do has no problems with a table that has no computed columns. But it is going to break when you take that same data and try to insert it back into the computeTbl.
I understand you don't want to list out all the columns, but I know of know way around this other than to ALTER TABLE to drop your calculated columns, do your deletes, and then recreate your computed columns. I doubt you want to do that, so just take a deep breath and list your columns. 😉
/*========================================================*/
CREATE TABLE ComputeTbl
(
[TransID][int]NOT NULL,
[Qty][int]NOT NULL,
[Amt][int]NOT NULL,
[ExtAmt] AS ([qty]*[amt])-- computed column
) --ON [PRIMARY]
CREATE TABLE deletions(
[TransID][int]NOT NULL,
[Qty][int]NOT NULL,
[Amt][int]NOT NULL,
[ExtAmt][int]NOT NULL-- NOT computed
) --ON [PRIMARY]
insert into computeTbl
select 1,5,10 union all -- only [TransID], [Qty] and [Amt] are being inserted, 3 columns not 4
select 2,6,5 union all
select 3,7,14 union all
select 4,3,22 union all
select 5,1,10 union all
select 6,5,13
GO
select * from computeTbl
GO
CREATE TRIGGER computeTbl_delete
ON computeTbl
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
insert into deletions
select * from deleted
END
GO
delete computeTbl where ExtAmt <=50
select * from computeTbl
order by transID
select * from deletions
order by TransID
GO
insert into computeTbl
select * from deletions
GO
drop table computeTbl
drop table deletions
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 10, 2008 at 11:35 pm
rd (11/7/2008)
Hi there,I like to keep several versions of a record in a table. To accomplish that I use the following SQL statement
BEGINIF object_id('tempdb..#T') IS NOT NULL
DROP TABLE #T
SELECT * INTO #T FROM deleted
UPDATE #T SET ID=NewID();
INSERT dbo.tblAgencies SELECT * FROM #T --(1)--
UPDATE dbo.tblAgencies
SET[Modified] = GetDate(),
[UserName] = suser_sname(),
[RID] = newid(),
[Version] = ((Select [Version] From inserted) + 1)
Where [ID] In (Select [ID] From inserted)
DROP TABLE #T
END
Now The statement fails on the INSERT by (1) the error is "Insert Error: Column name or number of supplied values does not match table definition."
Well i can imagine that as the tables are not exactly the same. Eg missing relation and or it is the calculated column which is a nvarchar in the temptable. A solution is to write out all the fields I like to copy (Always all) But that will be a bitch to maintain :ermm:
So is there a beter solution maybe. I was thinking to modify directly on the deleted table but SQL is not allowing that.
Maybe something on the fly while inserting.
Thx.
cant you use timestamp for the versioning of the records
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 11:56 pm
use "uniqueidentifier" in your table.
November 11, 2008 at 1:36 am
Kishore.P (11/10/2008)
use "uniqueidentifier" in your table.
Uniqueidentifier is a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
It cant use in the case of versioning of records as it lacks the date and time information
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 11, 2008 at 10:48 am
Guys, you're still not addressing his issue. He wants to insert deleted rows back into the same table with a version identifier, using SELECT * because he doesn't want to go back and change the trigger when he changes the table, but he can't because of the computed column.
How about storing the deleted rows in a separate archive table? Add a datetime column at the end for when it was archived and replace the computed column with one of a proper data type to just store the computed value at the time of deletion. If you need to see them together with the currently active versions, query both tables with a UNION ALL.
IMO: This is getting awfully complicated just to avoid typing in all the column names.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply