December 27, 2007 at 12:07 pm
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basically the same, the table names are the same, but the column schema is NOT.
Basically, I want to use a conditional on the Insert.
[font="Courier New"]Code Block
CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')[/font]
This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
Any help would be appreciated
December 27, 2007 at 1:28 pm
You don't have a "Fred" column in your target table, but you do have one in your insert statement. Fix that part, and you should have a working solution, I think.
- 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
December 27, 2007 at 2:17 pm
The create table statement is preventing this from being conditional. Try removing the create table statement from the query (after the table is created) and run the conditional insert with a begin and end.
begin tran
IF EXISTS
(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
begin
INSERT TestTable(Col1, Col2, Fred)
VALUES (1,'test', 'fredvalue')
end
else
begin
print 'no insert'
end
commit
December 27, 2007 at 2:23 pm
Unfortunately, your example results in the exact error I'm referring to. You still get the same issue.
That was just some example code. In the real environment, only a chunk of code like you posted would run. All tables already exist, I just do not know if the source table contains a given field or not.
December 27, 2007 at 2:44 pm
I don't think you have any althernative other than dynamic SQL.
SQL code is parsed. Then it is executed. All code is parsed, including both branches of an IF .. THEN ... ELSE, because the parser does not know which branch will execute.
The parser will always throw the error on the invalid column name, even if that code happens to be in a branch that you don't expect to execute.
December 27, 2007 at 2:53 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply