CREATE TABLE with variable name

Question

Post reply

CREATE TABLE with variable name

jamckin1

Old Hand

Points: 322
More actions
August 4, 2008 at 11:53 am

#196493

I am trying to create a table with the yyyymmdd GETDATE() format included in the table name. Can't seem to get the code correct - I know - rookie question, but I still need to get this solved relatively fast. Here is what I wrote thus far...
DECLARE @test-2 AS INT
SET @test-2 = CONVERT(CHAR(8), GETDATE(), 112)
CREATE TABLE server_name.dbo.Test + @test-2
(DeptVarChar(8)
, "Last, First MI" VarChar(50)
, EepAddressEMail VarChar(50)
, "Emp No."VarChar(9)
, "Job Code"VarChar(8)
, "Period"VarChar(8)
, "Earn code"VarChar(5)
, "Pay Rate"Decimal(16,6)
, HoursDecimal(16,6)
, AmountDecimal(5,2))
GO

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You need to use dynamic SQL In order to use variables. It would be like this:

[font="Courier New"]DECLARE @Test AS INT,

@sql VARCHAR(MAX)

SET @Test = CONVERT(CHAR(8), GETDATE(), 112)

SET @SQL = 'CREATE TABLE server_name.dbo.Test' + @Test + '

(Dept VarChar(8)

, "Last, First MI" VarChar(50)

, EepAddressEMail VarChar(50)

, "Emp No." VarChar(9)

, "Job Code" VarChar(8)

, "Period" VarChar(8)

, "Earn code" VarChar(5)

, "Pay Rate" Decimal(16,6)

, Hours Decimal(16,6)

, Amount Decimal(5,2))

GO'

EXEC (@SQL)

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jamckin1 Old Hand Points: 322 More actions · Answer 2

AHA! Thanks, I'll try that and let you know how that works!

jamckin1 Old Hand Points: 322 More actions · Answer 3

I tweaked it a hair, but it works great!! thanks very much!!

jamckin1 Old Hand Points: 322 More actions · Answer 4

Ok, SQL Taks Editor does not appear to like stored procedures. It's expecting a SELECT, UDATE, DELETE, etc... msg and fails when I have the following code inserted. How can I get a stored procedure to work in a Visual Studio SQL Task?

DECLARE @test-2 AS VARCHAR(8),

@sql VARCHAR(MAX)

SET @test-2 = CONVERT(CHAR(8), GETDATE(), 112)

SET @sql = 'CREATE TABLE server_name.dbo.Test_' + @test-2 + '

(Dept VarChar(8)

, "Last, First MI" VarChar(50)

, EepAddressEMail VarChar(50)

, "Emp No." VarChar(9)

, "Job Code" VarChar(8)

, "Period" VarChar(8)

, "Earn code" VarChar(5)

, "Pay Rate" Decimal(16,6)

, Hours Decimal(16,6)

, Amount Decimal(5,2))'

EXEC (@SQL)

GO