if your using SQL Svr 2016 or greater there is a built in spit string function

STRING_SPLIT ( string , separator )

declare @str varchar(500)
set @str ='0107602_000002008823_20150206_1_449589981255609050_20150206__20.26'


select row_number() over...

Collation is stored at DB level so when you restore your DB into the new server the collation will remain the same. You could look at altering the collation after...

what are you using the why loop for, what conditions? could you not re-write your query using merge instead of having separate insert and update statements?

you could look at incorporating isnumeric

declare @a varchar(100) = 'GUDR150305R02'
IF isnumeric(substring('GUDR150305R02',1,1))=0
Print 'T'
Else
Print 'F'

declare @a varchar(100) = 'GUDR150305R02'
select substring('GUDR150305R02',5,6)
IF isnumeric(substring('GUDR150305R02',5,6))=1
Print 'T'
Else
Print 'F'

oracle has equivalent information_schema

Select * from all_tables

select * from all_views

Select * from all_tab_columns

so you cant create a temp table but whoever created this SP can? politics

why not just convert the SP into a select and run your query before you drop the...

can you share you SP with us?

i guess your using bulk insert to read from the txt file and insert into a temp table?

what do you want use the data...

Have you created or are you creating the stored proc?

Why are you not allowed to use a temp table?

what do you want to do with the results of the query?

If...

I find Merge statements are very efficient for upserts (insert,update,delete) operations. could you share with us your execution plan? actual not estimated.

nice subject, I use coalesce more than often and its always been very useful

Try using  information_schema.columns instead of  sys objects

select * from INFORMATION_SCHEMA.COLUMNS

where  COLUMN_NAME in ('COLA','COLB','COLC')

why do you want to delete the data but keep the schema? a database without data is just a useless empty box

When you say you want to return all the 100's are you sating only return a row if the first 3 values are 100?

so if the data looked like this

100123

100124

101111

199111

200111

would...

if this SQL server is part of a vendor application, i would speak with the vendor before applying any patch. Just to ensure you are covered with your support contract...