INSERT AND SELECT and assign new value

[cfbeginner]

Hi All,

How do I insert a multiple row an existing table and assign a new value to one of the fields??

Below is my SQL query

Insert INTO tblCompany(CompanyID, Type, SoftwareID, LocationID)
SELECT CompanyID, Type, SoftwareID, LocationID
FROM tblcompany
Where LocationID=7

This works great, but I need to know how to assign a new value to the 'locationID=100'

Thanks very much!
Sout

 

[hneal98]

Insert INTO tblCompany(CompanyID, Type, SoftwareID, LocationID)
SELECT CompanyID, Type, SoftwareID, LocationID = 100
FROM tblcompany
Where LocationID=7

 

[cfbeginner]

Thanks for your help Hneal98!

Now I received the below error when I ran the query

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tblCompany' when IDENTITY_INSERT is set to OFF.

How do I resolve this??

 

[hneal98]

Ok. You didn't state that it was an Identity field. Why do you want to change the identity field? It automatically assigns a new value for you.

 

[cfbeginner]

Yes, the companyID is a identity field. Also it is replicated with other servers so there maybe problem updating/inserting over 1000 files at one time.

 

[hneal98]

not a good idea to use an identity column then. You should probably consider creating your own id. You can setup an id sequencer code that would go out and check what the latest id is.

You can use a julian date at the start of the id and then have a 5 digit number after that. then for each day, the records added will be unique. Also, for each replication, you can set it up so for one replication, it uses the 20,000 series, another would use 40,000, etc.