SQL Insert Into question

Question

Post reply

SQL Insert Into question

ruben.f.cosme.-nd

SSC Rookie

Points: 30
More actions
August 28, 2015 at 12:57 pm

#307228

Hello,
I am new to this forum and had a question about SQL Server 2012.
I keep getting the error ---> Msg 213, Level 16, State 1, Line 2 Column name or number of supplied values does not match table definition.
We just migrated to a Windows Server 2012 R2 server and this was working fine on Windows Server 2008 R2. I do not get the error above on the 2008 server.
Here are the statements I am trying to process: (And I did not write any of this. It's all legacy stuff that I now support.)
It's baffling to me why it would work on Windows Server 2008 and not 2012. Both servers run SQL Server 2012.
Thank you in advance for ANY direction you can provide.
CREATE TABLE [Metrics].[dbo].[x64_Restore_Metrics_All_Temp](
[date] [nvarchar](20) NULL,
[bytes] [bigint] NULL,
[Whos] [varchar](6) NULL
) ON [PRIMARY]
BULK INSERT [Metrics].[dbo].x64_Restore_Metrics_All_Temp
FROM 'D:\DMAC\All_Reports\Total_Restores_PROD.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
ALTER TABLE x64_Restore_Metrics_All_Temp ADD [Date1] [nvarchar](20) NULL
GO
update [Metrics].[dbo].[x64_Restore_Metrics_All_Temp] set Date1 = SUBSTRING(Date,4,2)+'/'+SUBSTRING(Date,7,2)+'/'+SUBSTRING(DATE,1,2)
GO
Insert Into dbo.x64_Restore_Metrics
SELECT Date1, sum(bytes/1073741824), COUNT(*)
FROM x64_Restore_Metrics_All_Temp
GROUP BY date1
HAVING COUNT(*) > 1
ORDER BY DATE1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you altered the table and added a new column [date1]

the table [Metrics].[dbo].[x64_Restore_Metrics_All_Temp] now has four columns, but your insert has only three.

if you do insert into table without a column list, your query has to match teh number of columns(that are not identity and not calculated)

just change to have the right columns you want to inser:

Insert Into [Metrics].[dbo].[x64_Restore_Metrics_All_Temp] ([date],[bytes],[Whos])

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ruben.f.cosme.-nd SSC Rookie Points: 30 More actions · Answer 2

Thank you very much. You got me going in the right direction. Had an issue with the identity column after your suggested changes but I got that resolved.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

For this reason and others, I always specify a column list when performing an insert. The exception to that rule is when I do a SELECT INTO.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions