i am doing a bulk insert: DECLARE @row_terminator CHAR; SET @row_terminator = CHAR(10); — or char(10) DECLARE @stmt NVARCHAR(2000); SET @stmt = ‘ BULK INSERT accn_errors FROM ”F:FullUnzipped…
Tag: sql-server
How to use regular expression in T-SQL?
For example: where strword match with {%{J{GC * GC}J} or strword={%{J{GC * GC}J}
How to insert an existing GUID into Oracle RAW(16) field in a script
I have an sql server script which inserts known fixed guid values into a table. It looks like: Note that guid is in human-readable form. Since ID is uniqueidentifier sql server understands how to convert a string to guid data type. I need to make the same script for Oracle, ID is of RAW(16) type. Taking the script directly doesn’t
How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them? Answer The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The
How to change only the year of a date datatype
I have a list of birthdays and the year is the only part that is incorrect. I have a list of ID #s for these individuals. Is there a way to change only the year for all of these people? I was thinking something like making a table of the query results and then using an UPDATE SET query, but
Use SQL Server recursive common table expression to get full path of all files in a folder(with subfolders)
There is a SQL Server undocumented extended stored procedure called xp_dirtree, which can return all files and folders name (include subfolders) in a table format. To practice my understanding of …
Most recent datetime column and count for each table
I have a DB that has 1000+ tables. 100 of those tables are prefixed with a three letters (let’s say ‘ABC’) Only half of those prefixed tables have MODIFIEDDATETIME column. I’m trying to do a simple select query to get all the last updated MODIFIEDDATETIME stamp for each Table that actually has a MODIFIEDDATETIME on that table and also begins
sp_MSforeachtable – parsing of dynamic sql
I recently found an issue whereby I wanted to use the sp_MSforeachtable stored proc to select all tables with the word Transcode in the table name, and to run some SQL on those tables. I managed to write some code which worked, but not perfectly – for those tables which I’d hoped it would gracefully skip over (i.e. those which
script issue Transact-SQL
I Want to return All table names from a use data base but this just return a char the output is s s s s s s Answer You need to define the length of @tableName, by default it is set to 1 character.
SQL Server Convert Varchar to Datetime
I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it? Answer