December 12, 2010 at 9:06 am
petervdkerk (12/12/2010)
Sorry if I wasnt clear, but by this "what I'd rather not have to do is individually insert the column names that are to be selected and inserted.", I mean:I want the statement to be flexible and if I later add columns to the database, I dont want to have to alter the query to include the newly added columns.
So thats why I want to use: *, instead of the actual column names.
Since the target table used in an OUTPUT ...INTO needs to be defined prior to the INTO statement, your approach would require a permanent rebuild of prospects_copy, e.g. by using SELECT 0 as row,prospects.* INTO prospects_copy FROM prospects WHERE 1=2
But that would also require to drop prospects_copy every time before you run this statement if the number of columns has changed.
The error you get is most probably causes by mismatch of the columns in prospects_copy and deleted.*
December 12, 2010 at 3:48 pm
It's still a bit of a strange request as your backup table would need to change to accommodate these new columns wouldn't it?
And how often are you planning on having this duplicate data problem anyway? surely once you have fixed this data you will be applying constraints to stop it happening again?
I can't see how you can do this using shorthand/shortcuts really...sorry.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
December 13, 2010 at 4:58 am
petervdkerk (12/12/2010)
I want the statement to be flexible and if I later add columns to the database, I dont want to have to alter the query to include the newly added columns.So thats why I want to use: *, instead of the actual column names.
And that's exactly why using * won't work for you.
SQL Server queries that use * tend to fail when the schema on the table changes. You will have to properly update the query each time you add or subtract columns, otherwise it'll error out. I've seen it happen. So, I'm sure, has MM and Lutz. Use the column names. You may not like it, but that's your only choice.
Of course, you could make this all dynamic SQL, using Coalesce() to come up with the column name list each time, but that's unnecessarily messy IMHO. Especially since you should, after cleanup, be implementing standards for clean data (as previously suggested).
December 13, 2010 at 6:57 am
petervdkerk (12/12/2010)
Sorry if I wasnt clear, but by this "what I'd rather not have to do is individually insert the column names that are to be selected and inserted.", I mean:I want the statement to be flexible and if I later add columns to the database, I dont want to have to alter the query to include the newly added columns.
So thats why I want to use: *, instead of the actual column names.
Don't bother. Include the columns this time, and then add a logging trigger to the table for future audits.
This kind of data-cleanup isn't really something that you run over and over. The script will pretty much be a one-off, and then you won't need it again.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply