Create a table, dynamically, in MS SQL.

I’m currently working a project that requires reading an MS Excel spreadsheet into a database table so I needed a stored procedure in MS SQL to create a table dynamically given the database name, table name and number of columns as an input. Re-using some prior work.

Code below:

USE [database name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 2016-01-05
-- Description:	Create Element Tables.
-- =============================================
CREATE PROCEDURE [dbo].[CreateElementTables]
 @Database varchar(255),
 @TableName varchar(255),
 @NumColumns int
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @sqltext varchar(max)
	DECLARE @x int = 0

	SET @sqltext = 'CREATE TABLE ' + @Database + '.[dbo].' + @TableName + '([RowNumber][int] IDENTITY(1,1) PRIMARY KEY,'

	WHILE @x < @NumColumns
	BEGIN
	 SET @x = @x + 1
	 SET @sqltext = @sqltext + '[C' + cast(@x as varchar(2)) + '] [varchar](max) NULL,'
	END

	SET @sqltext = @sqltext + ') ON [PRIMARY]'
	SET @sqltext = REPLACE(@sqltext,'NULL,)','NULL)')

	print @sqltext
	exec(@sqltext)
END

Send email via pHp

For a number of years I’ve used @adamGreenPress’s twitter enagement framework for interacting with my twitter community. I’ve found it useful for generating a summary of a tweets that have been sent while I’ve been away from the platform. It’s based on pHp on the front-end, running on CentOS/Apache, and the data store is mySQL.

I use a pHp script, running on a schedule, to send summaries to my GMail account.

The script is as follows:

require('config.php');
require('db_lib.php');
$db = new db();

$query = "select * from engagement.tweets where user_id in (select user_id from engagement.leaders where screen_name like '%nvestor%') order by created_at desc LIMIT 20";
$result = $db->select_array($query);

$email = 'example@domain.com';
$to = $email; //writing mail to the user
$subject = "Globe Investor";

$message = "<html>";
$message = $message."<table>";
$message = $message."<tr><td>";
foreach($result as $r) {
  $message = $message."<tr>".$r['tweet_text'].",[".$r['created_at']."]"."</tr>";
}
$message = $message."</table>";
$message = $message."</html>";

$from = "example@domain.com";
// To send HTML mail, the Content-type header must be set
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
// Additional headers
$headers .= 'From: Team <example@domain.com>' . "\r\n";

if(mail($to,$subject,$message,$headers))
{
        echo "0";// mail sent Successfully.
}
else
{
        echo "1";
}

The connection string for the database is in db_lib.php, and I issue the select statement to retrieve the desired tweets (in this case from @GlobeInvestor) from the database at line 5.

To send to Gmail I use MSMTP configured per How To Use Gmail or Yahoo with PHP mail() Function at Digital Ocean.