December 12, 2014 at 9:34 am
I would like to use code something like:
--
-- Table aaaa can be of any construction
-- The construction of table aaaa is not know at design time.
--
create table aaaa (
a int identity(100,7),
b varchar(300) null,
c varchar(100) null
)
insert into aaaa values('hallo', 'piet')
insert into aaaa values('hallo', 'jan')
insert into aaaa values('dag', 'piet')
select * from aaaa
-- Create the temp table. (Works fine)
select * into ##AAA from aaaa where 1 = 2
-- Insert a selection of the aaaa table in the temp table. (Oops Identity insert problems).
insert into ##AAA
select * from aaaa where b ='hallo'
select * from aaaa where c ='piet'
go
drop table aaaa
drop table ##AAA
Offcourse this is not working, because of the identity insert.
Can I remove the identity insert from the temporary table?
At design time the columnlist of table aaaa is not known, also at design time it is not known if table aaaa has an identity column. Generating columnlists would make my solution far more complex.
--
--
-- This table aaaa does work. (No identity column).
--
create table aaaa (
-- a int identity(100,7),
b varchar(300) null,
c varchar(100) null
)
Offcourse I can not change the aaaa tables I have. (They exist). The temptables are created (and removed) in the process.
Can anybody give a solution how to handle the temporary table ?
Thanks for your time and attention,
Ben
December 12, 2014 at 9:40 am
How do you not know table structures at design time.....
December 12, 2014 at 9:49 am
Your question does not really have enough clarity to offer much help. Why do you need to create temp tables from persistent tables like this? Perhaps if you can more clearly explain what you are trying to do we can offer more help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2014 at 10:01 am
ZZartin (12/12/2014)
How do you not know table structures at design time.....
I am building a generic tool. *)
The tool can be used on tables which are not in existence yet.
Ben
*)
More specific at the moment I am working on a tool which performs a Cascaded delete.
When an row is removed all children (and grandchildren etc. ect.) are removed first.
This works in any database as long as there are constraints (or if constraints are not not implemented they can be supplied). (Including conditional constraints).
The routine is only to be used with extreme caution, normal deletes are non cascading.
The temp tables are an extention to the Cascaded delete. With an option you can inspcet all rows to be deleted; to evalute the impact of the action before. The temptables are used to hold the rows.
December 12, 2014 at 10:06 am
ben.brugman (12/12/2014)
ZZartin (12/12/2014)
How do you not know table structures at design time.....I am building a generic tool. *)
The tool can be used on tables which are not in existence yet.
Ben
*)
More specific at the moment I am working on a tool which performs a Cascaded delete.
When an row is removed all children (and grandchildren etc. ect.) are removed first.
This works in any database as long as there are constraints (or if constraints are not not implemented they can be supplied). (Including conditional constraints).
The routine is only to be used with extreme caution, normal deletes are non cascading.
The temp tables are an extention to the Cascaded delete. With an option you can inspcet all rows to be deleted; to evalute the impact of the action before. The temptables are used to hold the rows.
Generic tools in a database are fraught with problems. They sound really cool but in practice they usually are pretty bad. They are incredibly difficult to build because they almost always require dynamic sql. The amount of things that need to be checked can get staggering. Trust me the identity property is just the tip of the iceberg. The other problem these types of generic solutions is that the performance is generally so awful the project gets abandoned because you can't really tune them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2014 at 10:24 am
Sean Lange (12/12/2014)
Generic tools in a database are fraught with problems. They sound really cool but in practice they usually are pretty bad. They are incredibly difficult to build because they almost always require dynamic sql. The amount of things that need to be checked can get staggering. Trust me the identity property is just the tip of the iceberg. The other problem these types of generic solutions is that the performance is generally so awful the project gets abandoned because you can't really tune them.
I am aware of those problems. The Cascaded delete I am now working on has been working great for almost 10 years.
At the moment I am extending the Cascaded delete with a Cascaded View to inspect the impact before it is executed. Also it can get a view off all data belonging (hierarchical) to a row or a set of rows.
In General the performance is not a problem.
And in general the constraints and the identity problems make the work interresting.
Especially in complex situations or when as now tool should work against any (or most) databases.
Asking the question is sometimes anwsering it. During these messages, I got an idea for a solution, still have to try it. If it works I'll show an example here.
(It's 18:30 here, office is getting cleaned, security will remove me from the building shortly. I'll try to post the solution this evening from home.).
Ben
December 12, 2014 at 11:37 am
Hello, thanks for your attention.
While messaging in this forum I came up with the following sollution:
-----------------------------------------------------------------------------
-- Solution method 1
-- Create a temptable of an actual table, where the identity column is not used as an identity column. Table aaaa can be any existing table. (?)
-----------------------------------------------------------------------------
-- Create the table
select * into ##AAA0 from aaaa where 1 = 2
select * into ##AAA from (
select * from ##AAA0
union all
select * from ##AAA0 ) XXX
-- Insert data.
insert into ##AAA select * from aaaa where b ='hallo'
insert into ##AAA select * from aaaa where c ='piet'
-- Any number of inserts can be performed.
-- Show data.
select * from ##AAA
-- Remove tables.
if object_id('tempdb..##AAA') is not null drop table ##AAA
if object_id('tempdb..##AAA0') is not null drop table ##AAA0
-----------------------------------------------------------------------------
The UNION (or UNION ALL) construction is used together with an into,
this get rid of the identity property while keeping the column.
Now the temp table can be filled with the proposed statements.
Rows from the aaaa table can appear more than once.
This is by design. (They can easely be removed).
There are some variations on this solution.
Thanks all for your time and attention.
If there are other or more elegant solutions please post them.
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply