Here is a quick and simple way of finding a value in a particular column and removing it:
1 |
UPDATE [Table_Name] SET ID = REPLACE(ID,'"',''); |
This looks for a quote (“) in the column named ID and removes it.
Here is a quick and simple way of finding a value in a particular column and removing it:
1 |
UPDATE [Table_Name] SET ID = REPLACE(ID,'"',''); |
This looks for a quote (“) in the column named ID and removes it.
Bulk Insert is a fairly common function, but not so common that I can remember the syntax every time. Here I’ve posted the version I use most:
1 2 3 4 5 6 7 |
BULK INSERT dbo.TableName FROM '\\share_location\File_Name.csv' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR ='\n' ) |
Note that Bulk Insert does not support skipping headers. The source file should not include headers otherwise the load will fail.
More about Bulk Insert:
I’ve found many scenarios where there are certain users or groups that require execute permissions for all stored procedures in a database. Rather then granting access to all the objects individually, not to mention new objects that are created after you’ve granted access, I prefer to create an executor role and assign their user account to the role. The following works for SQL 2005 and above:
1 2 3 |
CREATE ROLE db_executor GRANT EXECUTE TO db_executor |
Then grant the db_executor role to their user account. Done!