February 15, 2016 at 11:54 am
/*
Hi I'm not sure how to explain this but I've included script to demonstraight everything.
The DROP TABLE Works
The INSERT INTO works
The SELECT INTO works
However when they are all combined to run at once, SQL throughs this error msg
"Column name or number of supplied values does not match table definition."
ANY HELP WOULD BE APPRECIATED ..
CREATE TABLE Q1(
[QMMachineID] [INT] NOT NULL,
[QMID] [INT] NOT NULL,
[QMLogicalID] [VARCHAR](12) NULL,
[QGLogicalID] [VARCHAR](12) NULL,
[ClientMachineID] [INT] NULL,
[ClientID] [INT] NULL,
[StartDate] [INT] NULL,
[EndDate] [INT] NULL,
[VersionName] [VARCHAR](20) NULL,
[QuestionText] [VARCHAR](250) NULL,
[ExceptionText] [VARCHAR](250) NULL,
[DefaultAnswer] [VARCHAR](20) NULL,
[AnswerRequired] [TINYINT] NULL,
[Rating] [TINYINT] NULL,
[UserFactor] [SMALLINT] NULL,
[UserFactorFlag] [TINYINT] NULL,
[UserTable1] [VARCHAR](12) NULL,
[UserTable1Flag] [TINYINT] NULL,
[UserTable2] [VARCHAR](12) NULL,
[UserTable2Flag] [TINYINT] NULL,
[Inactive] [TINYINT] NULL,
[DateAdded] [INT] NULL,
[DateLastUsed] [INT] NULL,
[UpdateDateTime] [INT] NULL,
[UpdateMachineID] [INT] NULL,
[UserMachineID] [INT] NULL,
[UserID] [INT] NULL,
[LastMachineID] [INT] NULL,
[LastDateTime] [INT] NULL,
[UserTable3] [VARCHAR](12) NULL,
[UserTable3Flag] [TINYINT] NULL,
[UserTable4] [VARCHAR](12) NULL,
[UserTable4Flag] [TINYINT] NULL,
[UserTable5] [VARCHAR](12) NULL,
[UserTable5Flag] [TINYINT] NULL,
[UserTable6] [VARCHAR](12) NULL,
[UserTable6Flag] [TINYINT] NULL,
[UserTable7] [VARCHAR](12) NULL,
[UserTable7Flag] [TINYINT] NULL,
[UserTable8] [VARCHAR](12) NULL,
[UserTable8Flag] [TINYINT] NULL,
[UserTable9] [VARCHAR](12) NULL,
[UserTable9Flag] [TINYINT] NULL,
[UserTable10] [VARCHAR](12) NULL,
[UserTable10Flag] [TINYINT] NULL
) ON [PRIMARY]
GO
CREATE TABLE Q2(
[TableName] [VARCHAR](9) NOT NULL,
[QMMachineID] [INT] NOT NULL,
[QMID] [INT] NOT NULL,
[QMLogicalID] [VARCHAR](12) NULL,
[QGLogicalID] [VARCHAR](12) NULL,
[ClientMachineID] [INT] NULL,
[ClientID] [INT] NULL,
[StartDate] [INT] NULL,
[EndDate] [INT] NULL,
[VersionName] [VARCHAR](20) NULL,
[QuestionText] [VARCHAR](250) NULL,
[ExceptionText] [VARCHAR](250) NULL,
[DefaultAnswer] [VARCHAR](20) NULL,
[AnswerRequired] [TINYINT] NULL,
[Rating] [TINYINT] NULL,
[UserFactor] [SMALLINT] NULL,
[UserFactorFlag] [TINYINT] NULL,
[UserTable1] [VARCHAR](12) NULL,
[UserTable1Flag] [TINYINT] NULL,
[UserTable2] [VARCHAR](12) NULL,
[UserTable2Flag] [TINYINT] NULL,
[Inactive] [TINYINT] NULL,
[DateAdded] [INT] NULL,
[DateLastUsed] [INT] NULL,
[UpdateDateTime] [INT] NULL,
[UpdateMachineID] [INT] NULL,
[UserMachineID] [INT] NULL,
[UserID] [INT] NULL,
[LastMachineID] [INT] NULL,
[LastDateTime] [INT] NULL,
[UserTable3] [VARCHAR](12) NULL,
[UserTable3Flag] [TINYINT] NULL,
[UserTable4] [VARCHAR](12) NULL,
[UserTable4Flag] [TINYINT] NULL,
[UserTable5] [VARCHAR](12) NULL,
[UserTable5Flag] [TINYINT] NULL,
[UserTable6] [VARCHAR](12) NULL,
[UserTable6Flag] [TINYINT] NULL,
[UserTable7] [VARCHAR](12) NULL,
[UserTable7Flag] [TINYINT] NULL,
[UserTable8] [VARCHAR](12) NULL,
[UserTable8Flag] [TINYINT] NULL,
[UserTable9] [VARCHAR](12) NULL,
[UserTable9Flag] [TINYINT] NULL,
[UserTable10] [VARCHAR](12) NULL,
[UserTable10Flag] [TINYINT] NULL
) ON [PRIMARY]
GO
SELECT * FROM Q1
SELECT * FROM Q2
DROP TABLE Q1;
DROP TABLE Q2;
*/
IF NOT EXISTS (SELECT name FROM sys.objects WHERE TYPE='U' AND name = 'Q1')
BEGIN
PRINT 'Please connect to the Proper Database and run this again'
END
ELSE
BEGIN
------------------------ THIS SECTION BY ITSELF WORKS
IF OBJECT_ID('dbo.Q2') IS NOT NULL
AND COLUMNPROPERTY( OBJECT_ID('dbo.Q2'),'CrtDT','ColumnId') IS NULL
DROP TABLE Q2;
------------------------ THIS SECTION BY ITSELF WORKS
IF OBJECT_ID('Q2') IS NOT NULL
BEGIN
INSERT INTO Q2
SELECT
TableName = 'Questions',
CrtDT = GETDATE(),
*
FROM Q1 WHERE QuestionText LIKE '%Orphan%'
END
ELSE
BEGIN
SELECT
TableName = 'Questions',
CrtDT = GETDATE(),
*
INTO Q2 FROM Q1 WHERE QuestionText LIKE '%Orphan%'
END
END
February 16, 2016 at 7:04 am
I cannot help you with the problem, but I have a guess at what it is. SQL evaluates the query before executing it so even thought you have logic to drop a table it does not and then the code is not correct.
I am sure anyone else can explain this better, but this might give you an idea of where to look.
February 16, 2016 at 12:54 pm
Djj is right. The entire batch is checked before execution starts, and the insert into at the end does not match the Q2 table that exists when the batch starts.
If you separate the statements with "go" batch separators, I expect it to work.
February 16, 2016 at 2:32 pm
Yes, I understand that separating the transactions with a GO will make it work, however this is but one part of a larger transaction that I was trying to complete as one. I will attempt to break it out as a separate transaction.
Thanks to all respondents.
February 16, 2016 at 9:03 pm
It's not about the tables, it's about the columns.
When parsing a query SQL Server does not check if the table exists or not, but it validates the schema for all existing tables.
The columns in both initially existing tables Q1 and Q2 do not match, so INSERT INTO statement fails parsing.
If you start the batch after dropping the table there would not be any issue.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply