Redshift query to generate UNLOAD and COPY statements

Below is a statement that can be used to generate a list of commands to copy all tables from Redshift to S3.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' UNLOAD (''SELECT * FROM ' ||
"table" ||
''')' ||
' TO ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role 'arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimter ''|'' addquotes escape allowoverwrite;'
FROM SVV_TABLE_INFO;

In a situation where you want use to copy data into a different redshift cluster, the following will create the copy commands that can be used to copy data from S3 to Redshift.  Please note that you'll need to substitute YOUR_BUCKET_NAME, YOUR_ACCOUNT_ID and YOUR_ROLE placeholders with the values appropriate for your environment.

SELECT
' COPY ' ||
"table" ||
' FROM ''s3://YOUR_BUCKET_NAME/' ||
"table" ||
'/''' ||
' iam_role ''arn:aws:iam::YOUR_ACCOUNT_ID:role/YOUR_ROLE''' ||
' delimiter ''|'' removequotes escape '';'
FROM SVV_TABLE_INFO;

SQL Server Date Formatting

I always feel like I'm digging around to find a list of date formats for SQL Server.  I then end up doing some trial and error to get the desired format.

Below is helpful list to refer to for common date formatting.

Format Code	Output
default	2011-10-20 06:59:22.590
convert(varchar,getDate(),100)	Oct 20 2011 6:59AM
convert(varchar,getDate(),101)	10/20/2011
convert(varchar,getDate(),102)	2011.10.20
convert(varchar,getDate(),103)	20/10/2011
convert(varchar,getDate(),104)	20.10.2011
convert(varchar,getDate(),105)	20-10-2011
convert(varchar,getDate(),106)	20 Oct 2011
convert(varchar,getDate(),107)	Oct 20, 2011
convert(varchar,getDate(),108)	06:59:00
convert(varchar,getDate(),109)	Oct 20 2011 6:59:00:553AM
convert(varchar,getDate(),110)	10-20-2011
convert(varchar,getDate(),111)	2011/10/20
convert(varchar,getDate(),112)	20111020
convert(varchar,getDate(),113)	20 Oct 2011 06:59:00:553
convert(varchar,getDate(),114)	06:59:00:553
convert(varchar,getDate(),120)	2011-10-20 06:59:00
convert(varchar,getDate(),121)	2011-10-20 06:59:00.553
convert(varchar,getDate(),126)	2011-10-20T06:59:00.553
convert(varchar,getDate(),127)	2011-10-20T06:59:00.553
convert(varchar,getDate(),130)	23 ?? ?????? 1432 6:59:00:553
convert(varchar,getDate(),131)	23/11/1432 6:59:00:553AM