can't do some inserts

You have specified car_id in the INSERT, yet it is an identity. Either
remove car_id and its corresponding value or SET IDENTITY_INSERT ON for that
table.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I can't do these inserts into INTERNAL_CAR table. I get an error on
line 2. It says near the ",". I don't know which "," has the error.
I have a lot of inserts to do.

INSERT INTO INTERNAL_CAR (car_id, equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(605, NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II railcars', 'for lease'),
(532, NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease'),
(533, NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease'),
(534, NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease'),

Here is the table definition:

CREATE TABLE[INTERNAL_CAR](
[car_id]int NOT NULL identity (1,1),
[equipment_id]int default NULL,
[aar_id]int default NULL,
[build_date]datetime default NULL,
[insp_date]datetime default NULL,
[man_id]int default NULL,
[tot_weight]int default NULL,
[light_weight]int default NULL,
[load_limit]int default NULL,
[cubic_capacity]int NOT NULL default '0',
[comments]text,
[car_type]varchar(15), CHECK ( car_type IN ('for lease','managed')) ,
PRIMARY KEY (car_id),

UNique(car_id, equipment_id)

Reply to this Message

The current versions of SQL Server do not support row constructors. It will
be available in SQL Server 2008. For now you have to repeat the INSERT
statement for each row of values. Example below with the first two rows:

INSERT INTO INTERNAL_CAR (car_id, equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(605, NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II railcars', 'for lease')

INSERT INTO INTERNAL_CAR (car_id, equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(532, NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Janis Rough - 10 Jul 2008 20:39 GMT

> The current versions of SQL Server do not support row constructors. It will
> be available in SQL Server 2008. For now you have to repeat the INSERT
[quoted text clipped - 16 lines]
>
> Plamen Ratchevhttp://www.SQLStudio.com

that isn't very good on 100's of rows.

Reply to this Message

thejamie - 10 Jul 2008 22:18 GMT

Could you apply them with a cursor?

For example,
declare @car_id int,@equipment_id int, @item1 varchar(50),@item2
varchar(50)... etc
Declare mycurs Cursor for select car_id,equipmnet_id,item1,item2... from
mysourcetableforinserting
open mucurs
fetch next from mycurs into @car_id,@equipment_id,@item1,@item2

while fetch_status=0
begin
insert into mydestinationtable(item1,item2) values (@item1,@item2) where
car_id=@car_id and equipment_id=@equipment_id
Fetch next from mycurs into @car_id,@equipment_id,@item1,@item2

end
close mycurs
deallocate mycurs

HTH...

Signature

Regards,
Jamie

> > The current versions of SQL Server do not support row constructors. It will
> > be available in SQL Server 2008. For now you have to repeat the INSERT
[quoted text clipped - 18 lines]
>
> that isn't very good on 100's of rows.

Reply to this Message

thejamie - 10 Jul 2008 22:26 GMT

You can also update a table with Primary and foreign keys fairly easily and
much more quickly by using an update statement.

For example:
If you can see that you have the right information in the following query:
(I don't know your structure so the abc below is random)

Select A.car_id, B.equipment_id, C.aar_id, B.build_date,
B.insp_date, B.man_id, B.tot_weight, B.light_weight, B.load_limit,
B.cubic_capacity, C.comments, A.car_type
from cartable a inner join equiptable b on a.car_id=b.car_id inner join
aar c on c.equipment_id=b.equipment_id

and it appears correct then change up the lines:

--Select A.car_id, B.equipment_id, C.aar_id,
Update A set A.build__date=B.build_date,
A.insp_date=B.insp_date,
--B.man_id,
A.tot_weight=B.tot_weight, A.light_weight=B.light_weight,
A.load_limit=B.load_limit,
A.cubic_capacity=B.cubic_capacity, A.Comments=C.comment
--, A.car_type
from cartable a inner join equiptable b on a.car_id=b.car_id inner join
aar c on c.equipment_id=b.equipment_id

HTH

Signature

Regards,
Jamie

> Could you apply them with a cursor?
>
[quoted text clipped - 40 lines]
> >
> > that isn't very good on 100's of rows.

Reply to this Message

I believe you need an insert statement for each row.
You have also devine 'car_id' as an identity column so you cannot provide a
value on insert, the database will provide one according to your seed
values. If you need to insert specific values either don't define it as an
identity or you can use 'SET' statements to allow specific values.

The following works:
INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II railcars', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

Reply to this Message

I believe you need an insert statement for each row.
You have also devine 'car_id' as an identity column so you cannot provide a
value on insert, the database will provide one according to your seed
values. If you need to insert specific values either don't define it as an
identity or you can use 'SET' statements to allow specific values.

The following works:
INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II railcars', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

INSERT INTO INTERNAL_CAR (equipment_id, aar_id, build_date,
insp_date, man_id, tot_weight, light_weight, load_limit,
cubic_capacity, comments, car_type) VALUES
(NULL, 980, '2006-03-01', NULL, 5, 286000, 56000, 230000, 0,
'3,220 cu. ft. covered hopper, 286 GRL with three (3) 30 inch diameter
hatches and outlet gates.', 'for lease');

Reply to this Message