queries

#1 Error seen with query- INSERT INTO estimates SELECT * FROM estimate_bak; --Table estimates has an identity column named 'id' 

Msg 8101, Level 16, State 1, Line 5

An explicit value for the identity column in table 'Estimates' can only be specified when a column list is used and IDENTITY_INSERT is ON.

soln- Specifiy column list to insert data into. INSERT INTO estmates(id, service_type....) SELECT id, service_type FROM estimate_bak;   

#2 Another error follows - 

Msg 544, Level 16, State 1, Line 5

Cannot insert explicit value for identity column in table 'Estimates' when IDENTITY_INSERT is set to OFF.

soln- As error message hints - SET IDENTITY_INSERT estimates ON;   

#3 Column to csv; example - Fetch column list to use in select query

soln- SELECT STRING_AGG(column_name, ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'estimates';  

#4 Add column to table; in MSSQL new column is appended at last only. if different ordinal is needed for new column, we need to recreate table using steps in soln 4.b. 

soln- a. ALTER TABLE JobCardPartIssues ADD TotalTax DECIMAL(8, 2);

b. steps 1.  SP_RENAME table_current, table_bak

2. SELECT col1, col2, new_col, col3, ... INTO table_current FROM table_bak;

3. DROP TABLE table_bak --after verifying table_current.

#5 Update a table joining another(lookup value in another table). 

soln- UPDATE x SET TotalTax = TaxableAmount * y.TotalTax/100 FROM JobCardPartIssues x INNER JOIN TaxMaster y ON x.TaxID = y.ID; 

#6 Data truncation error - 

Msg 8115, Level 16, State 8, Line 24

Arithmetic overflow error converting numeric to data type numeric.

soln- Had to increase precision of target column.  as in #7. Calculation was yielding value of higher precision than specified datatype of target column.  To see what is the value that is breaking, executed - SELECT MAX(TaxableAmount * y.TotalTax/100)  FROM JobCardPartIssues x INNER JOIN TaxMaster y ON x.TaxID = y.ID; 

#7 Update column datatype - 

soln- ALTER TABLE JobCardPartIssues ALTER COLUMN TotalTax DECIMAL(12,2);  --earlier type was decimal(8, 2); 

#8

soln-

#9

soln-