Insert rows into a table

[verynewbie]

Hi,
I'm fairly new with this SQL stuff. Need help on writing a simple script to add new rows into a table. Any help would be appreciated.

Here is what I have done so far to test things out on 2 mock tables. I was successful in creating the 2 tables and inserting values to them. Where I am stuck at is when I tried to insert the rows into the table.

This is the error I got
"Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition."

-------------------------------------------------------
create table Test_Users(
ID int not null,Username nvarchar(250),Fname nvarchar(250),Lname nvarchar(250))
go

insert Test_Users values (8, 'marys@yahoo.ca','Mary','Smith')
insert Test_Users values (9, 'joeb@hotmail.com','Joe','Blow')
insert Test_Users values (10, 'mikew@gmai.com','Mike','Williams')
go

create table New_Inserts (
ID int not null,
Username nvarchar(250),Fname nvarchar(250),Lname nvarchar(250))
go

insert New_Inserts values (11,'helenm','Helen','Make')
insert New_Inserts values (12,'wills','Will','Smith')
insert New_Inserts values (13,'bobb@yahoo.com','Bob','Barker')
go

select * from Test_Users
select * from New_Inserts

-- First do the updates
update Test_Users
set ID = ni.ID
select *
from Test_Users as tu
join new_inserts as ni
on tu.ID=ni.ID
go

-- Then insert new records
insert into Test_Users
select ni.Username,ni.Fname,ni.Lname
from New_inserts as ni
left outer join Test_users as tu
on ni.ID=tu.ID
where tu.ID is null
go

-- test if OK
select * from Test_users
go
---------------------------------------------------

 

[RiverGuy]

You need to post SQL Server specific questions in the SQL Server forum here: forum183

But to answer your question--your Test_Users table has 4 columns, and you are trying to insert data with 3 columns. Where is your ID column?

 

[JarlH]

You are trying to insert 3 column values into a table, but the table Test_Users has 4 columns. One value is missing! (Probably id.)

[tt]insert into Test_Users
select ni.Username,ni.Fname,ni.Lname
from New_inserts as ni ...[/tt]

 

[SQLSister]

IN sql server you may want to create your id column as an identity and then the insert will work correctly becasue it wuill automatically create the id. Hoever if you do that your queries with the values clause will not work. This is becasue once you are setting the column automatically you cannot manually insert it without running another command and you would not want to do that unless the table is in development and you are initally populting it with historical data becaseu once you turn off the identity insert any users trying to insert will be affected not just yourself.

An additonal way you can fix you problem is to specify the columns in the insert part of the statement (which I personally always do for clarity) and make the column allow nulls.

"NOTHING is more important in a database than integrity." ESquared