Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table. myTestFormatFiles.Fmt may look like: 9.0 4 1 SQLINT 0 3 “,” 1 StudentNo “” 2 SQLCHAR 0 100 “,” 2 FirstName SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 “,” 3 LastName SQL_Latin1_General_CP1_CI_AS 4 SQLINT 0 4 … Read more
You should place parenthesis around the selects: coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) )
You could do it in two ways. Method 1 Using LPAD. For example, SQL> WITH DATA(num) AS( 2 SELECT 540 FROM dual UNION ALL 3 SELECT 60 FROM dual UNION ALL 4 SELECT 2 FROM dual 5 ) 6 SELECT num, lpad(num, 5, ‘0’) num_pad FROM DATA; NUM NUM_P ———- —– 540 00540 60 00060 … Read more
Use NVARCHAR(size) datatype and prefix string literal with N: CREATE TABLE #tab(col NVARCHAR(100)); INSERT INTO #tab(col) VALUES (N’👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴 😭’); SELECT * FROM #tab; db<>fiddle demo Output: ╔═════════════════════════════════╗ ║ col ║ ╠═════════════════════════════════╣ ║ 👍 🖒 🖓 🖕 🗑 🛦 ⁉ 😎 😔 😇 😥 😴😭 … Read more
The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer, one text. With the current table, you could: SELECT col FROM tbl ORDER BY (substring(col, ‘^[0-9]+’))::int — cast to integer , substring(col, ‘[^0-9_].*$’); — works as text The same substring() … Read more
You can use information from the catalog tables like so: Create a table with id as the primary key create table test1 (id int primary key, name text); Create the SQL to drop the key select concat(‘alter table public.test1 drop constraint ‘, constraint_name) as my_query from information_schema.table_constraints where table_schema=”public” and table_name=”test1″ and constraint_type=”PRIMARY KEY”; The … Read more
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS
I’m a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
Use the DATEADD function: SELECT DATEADD(ss, 1291388960, ‘19700101’) …specifying a date of January 1st, 1970. In this example, it was provided in the YYYYMMDD format. DATEADD will return a DATETIME data type, so if you have a table & column established — you can use the function to INSERT/UPDATE depending on your needs. Provide details, … Read more