CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

How to insert a select into a table with an auto increment parameter?

I'm copying data from an old database to a new one and some things changed and I need to copy the reference key value to the other one. It's a one to one relation so the ID's should match hence why i wanted to use an auto increment but I can't figure out how to do it.

this is my query

INSERT INTO hMetodologiaProducao (TipoMetodologia, QTDMetodoProducao, IdArtigo)
SELECT hm.TipoMetodologia, hm.QTDMetProd, auto-increment
FROM FolhaRegisto.dbo.hMetodologiaProducao hm
WHERE hm.IdMetProd != 16 AND hm.IdMetProd != 17 AND hm.IdMetProd != 4 AND hm.IdMetProd != 5

how can i do this?

EDIT. The values i need on where auto increment is, is the identity value from another table, can be row number since seed started from 0 becaue it's a fresh new database

UPDATE

It's a bit hard to explain what i want to do but, this is basically what i want to do

INSERT INTO hMetodologiaProducao (TipoMetodologia, QTDMetodoProducao, IdArtigo)
SELECT hm.TipoMetodologia, hm.QTDMetProd, (Select IdArtigo From hArtigos)
FROM FolhaRegisto.dbo.hMetodologiaProducao hm
JOIN hArtigos ha ON ha.IdArtigo = hm.IdMetProd
WHERE hm.IdMetProd != 16 AND hm.IdMetProd != 17 AND hm.IdMetProd != 4 AND hm.IdMetProd != 5

I want to insert in the 3rd column the IdArtigo from anothter table, but this way it won't allow me because it says there's too many values.

I also tried using a join but I can't because the ID values are different and some rows will end up with wrong ID

NEW QUERY

I found out something that does almost what i need

INSERT INTO hMetodologiaProducao (TipoMetodologia, QTDMetodoProducao, IdArtigo)
SELECT hm.TipoMetodologia, hm.QTDMetProd, ha.IdArtigo
FROM FolhaRegisto.dbo.hMetodologiaProducao hm
CROSS JOIN hArtigos ha
WHERE hm.IdMetProd != 16 AND hm.IdMetProd != 17 AND hm.IdMetProd != 4 AND hm.IdMetProd != 5

However it's inserting all the values for each value from 1st table so it gives me foreign key constraint error, how can i do it so it repeats only once?

Final Edit

Possible solution, this does exactly what i wanted. I just used distinct so it doesnt repeat values, althought i'm not sure if this is correct to do

INSERT INTO hMetodologiaProducao (TipoMetodologia, QTDMetodoProducao, IdArtigo)
SELECT DISTINCT hm.TipoMetodologia, hm.QTDMetProd, ha.IdArtigo
FROM FolhaRegisto.dbo.hMetodologiaProducao hm
CROSS JOIN hArtigos ha
WHERE hm.IdMetProd != 16 AND hm.IdMetProd != 17 AND hm.IdMetProd != 4 AND hm.IdMetProd != 5

Upvotes: 0

Views: 148

Answers (1)

Suraj Kumar
Suraj Kumar

Reputation: 5643

I think if I am right you want to insert values in Identity column. You can do it in the following way.

Create table MyTable (Id int identity(1, 1), Name Varchar(20))

Set identity_insert MyTable on
insert MyTable(Id, Name) values (1 , 'A'), (2, 'B')

Select * from MyTable

Upvotes: 1

Related Questions