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@gmail.ca','Helen','Make')
insert New_Inserts values (12,'wills@ncc.com','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
---------------------------------------------------

 

[ethorn10]

On your last insert block you are attempting to insert less columns than the table has defined. So, you need to specify which columns you are inserting into:

-- Then insert new records
insert into Test_Users[COLOR=red](Username, Fname, Lname)[/color]
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

 

[pmegan]

You have a table with 4 columns but you're only inserting 3 values.

-- 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

You can modify the insert a little do this, as long as the column you're not inserting into has a default value; the ID field in this case. I always prefer putting the column names in the insert anyway. It just seems like its too easy to make mistakes using implied columns.

-- Then insert new records
insert into Test_Users(Username, Fname, Lname )
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

 

[verynewbie]

Thank you!
It works!