Thursday, July 25, 2019

Load image to SQL


INSERT INTO [Images]
           ([ImageId]
           ,[FileName]
           ,[FileSize]
           ,[ImageExt]
           ,[CreatedDateTime]
           ,[ModifiedDateTime]
           ,[CreatedBy]
           ,[ModifiedBy]
           ,[CompanyId]
           ,[ImageData])
     select
           'Test3'
           ,'Test3'
           ,'Test3'
           ,'jpg'
           ,'1/1/2019'
           ,'1/1/2019'
           ,'Test'
           ,'Test'
           ,'Test'
   ,bulkColumn FROM OPENROWSET(BULK N'e:\temp\monica\flowers.jpg', SINGLE_BLOB) image

Thursday, July 18, 2019

SQL Cursor

DECLARE @SQL varchar(max)
DECLARE @MyCursor CURSOR
DECLARE @TableName char(50)

--Get a list of all tables in DB
if object_id('tempdb..#tableList') is not null
drop table #tableList
select name, object_id into #tableList from sys.objects where type='u' and name not like 'sys%'

--Display the top 3 records of each table
SET @MyCursor = CURSOR FOR
 select name  from #tableList

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = ' SELECT TOP 3 * FROM  ' + rtrim(ltrim(@TableName))

EXEC(@SQL)

FETCH NEXT FROM @MyCursor
INTO @TableName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor