July 26, 2012 at 10:21 am
Can anyone help me to find the answer to the below
I have table with more than 20 columns, from this table I have to select and insert that in the same table.
Like the below example.
Employee table - Id uniqueidentifier,Name varchar(200),Email varchar(100)
Insert into Employee
Select NEWID() as Id, * from Employee where Id=@Id
I know the above query throws error message.
But I dont want the column Id value from Table, for that I subsitute with NEWID(), and also I dont want to mention the rest of the columns instead i will add "*" after comma.
Is their any other solutions instead of using the below query
Insert into Employee
Select NEWID() as Id, Name,Email from Employee where Id=@Id;
I dont want to mention rest of the 19 columns from the table.
July 26, 2012 at 10:31 am
The proper way to write an Insert Select statement is to list the target and source columns explicitly. Leave out any columns you don't want explicit values for (like Identity columns or columns with default values), but otherwise list them all.
First, you can't use * to indicate anything other than "all the columns". There isn't a "all the columns except the ones I want for something else", like what you're trying to do. There's no way for the computer to know which columns you do want and which ones you don't, except for you to list them.
Second, if the table definition on either side of an Insert Select (source or destination) changes, your code will break. Explicit column lists will continue to work as expected, but implict ones will break. That can be a real problem in production systems. Using implicit columns saves you a few seconds of work now, at the cost of creating hours or even days of work later when you have to track down errors and retroactively fix them.
Third, you can get a full list of columns very easily. Takes a few seconds. Then delete the ones you don't want. You can drag-and-drop the Columns "folder" in SSMS onto the window where you are writing the Insert statement, and it will list the columns for you, in sequence. If you have something like RedGate's SQL Prompt, you don't even have to do that, because you can type "Select *" and hit the Tab key on your keyboard, and it will expand the * into a full list of columns for you.
Alternatively, you can right-click the table in the object explorer in SSMS, and select "Script Table As..." and pick "Insert", or "Select", and it will write the Insert statement for you or the Select statement for you. Copy-and-paste the two together, and you have your Insert Select without having to type out the column names manually.
So, do your future-self a favor, and use one of those methods (whichever you are most comfortable with), to write out a full, explicit Insert Select. You'll save a lot of future work for yourself, and for others who work in that database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply