Delete records from Table and insert new records.

[Moordoom]

I am trying to delete all records from a table in one database, and repopulate it with a select statement retrieving data from another database.

This is what I have so far...

DELETE * FROM Database2.Table3;
INSERT INTO Database2.Table3
SELECT * from Table1
join Database2.Table2
on ColumnX = ColumnY

But I get the following error...
Msg 213, Level 16, State 1, Line 2
Column name or number of supplied values does not match table definition.

What I am wanting is the Data Records in Database1, Table1, and I am matching a column from it to Database2, Table2, and then inserting that matched data into Database2, Table 3.

But with the INSERT Statement, I am thinking I need Values as well, but I want ALL records that match, and there are like 25+ columns, so it would be a very lengthy Values().

What am I missing here?

 

[jmcilhinney]

The error message is telling you that the number of columns that you're selecting from the source table is not the same as the number of columns in the destination table. I suggest that you specify the columns in each case rather than relying on the database to map them for you, which it obviously can't do in this case.

 

[Moordoom]

I imported data from the Database1, Table1 with headers to Database2, Table3 so that it has data to start with along with the correct column names.
the query goes thru and clears the database with the Delete statement; leaving the column header with column names, but I still get that error same error.

And I should state that I am using SQL 2012 R2

 

[jmcilhinney]

If the error message states that the number of columns doesn't match then I can only conclude that, barring some corruption, the number of columns doesn't match. You might think it does but this would not be the first time that something is not as someone thought it was. If you specify the column names then you know exactly what columns are being used and which maps to which, so it's easy to see where the issue is if there is one. If you rely on the system doing things implicitly for you then you can only guess. I'd say that the most likely issue is that both the columns being joined on are included in the output while there's only one corresponding destination column. Regardless, if you specify the columns and you match up the number and data types then it will work, plain and simple.

 

[Moordoom]

Ok, I have gone in and deleted the old table, created a brand new table. Manually typed, and triple check each column name and data type to match the database I am pulling the records from. The only thing I did not set was the primary and foreign keys in the source database/table to be the same in the target database/table. As I am just pulling the records from Database1, I would think I do not need any keys.
So is it possible then that Database1.Table1 has incorrect data for the data types in its records (like putting a varchar in a numeric)?
Database1 was imported from SQL 2000SP4, to a SQL 2008 R2, then backed up and imported to a SQL 2012 R2 server.

 

[Moordoom]

Ok, I got it to work, but I did not like my change...
Here is what I did, but I cannot under stand why it would not work the way above...

DELETE * FROM Database2.Table3;
INSERT INTO Database2.Table3 (column1, column2, column3,......, column25)
SELECT columnA, columnB, columnC,......., columnY from Table1
join Database2.Table2
on ColumnX1 = ColumnY2

Seems like there would be an easier way like I did above....

 

[jmcilhinney]

I already told you why it didn't work before and so did the error message. You just don't want to believe it.