Variables in T-SQL

20 pts.
Tags:
SQL
T-SQL
Hi, I want to know y does the following script run in SQL and not in T-SQL DECLARE @tblName varchar(30) SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + 'Table' DECLARE @sql nvarchar(4000) SELECT @sql = 'CREATE TABLE "' + @tblName + '" ( ID VARCHAR(15), Name VARCHAR(15) )' EXEC(@sql) go it gives you the error Msg 170, Sev 15: Line 1: Incorrect syntax near '20090714Table'. [SQLSTATE 42000]
ASKED: July 14, 2009  12:33 PM
UPDATED: July 15, 2009  5:01 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The alert is being generated because you have Quoted Identifiers off when you are running it.

Remove the double quotes and replace them with square brackets. In SQL Server the double quote isn’t the default separator. This would make your code look more like this.

<pre>DECLARE @tblName varchar(30) SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + ‘Table’

DECLARE @sql nvarchar(4000) SELECT @sql = ‘CREATE TABLE [' + @tblName + '] ( ID VARCHAR(15), Name VARCHAR(15) )’

EXEC(@sql)</pre>

It is a very bad practice to use a single table per day, as your system maintenance will become very unwieldy very quickly.

Hi Mr Denny,

Thanks very much for the solution. It works!! You are the man

Regarding using a table/day, here is my situation. May be you can help me find a better solution.

I am using BULK INSERT to import csv files into the db. Everyday 1 csv file needs to be imported. The number of records are around 0.5M each day without any date/time stamp. I only need the date for these 0.5M records so that i can perform queries on it later on. I need to keep previous 30 days data stored in the db.

One solution could be to insert datetime while doing the bulk insert with each record. Is it possible and efficient?

Any other solutions?

P.S i couldnt find how to comment on the answer you provided so i had to improve it :)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I tested your code, and it works for me (SQL Server 2008).
    65,110 pointsBadges:
    report
  • Denny Cherry
    There's a Discussion section further down the page. The normal technique to use, would be to load the data into a staging table which is the same format as the text file. Then move it into the production table which has all the extra columns like primary keys, date fields, etc. That or use SSIS to import the file, adding in the extra columns as it processes. I prefer the staging table technique.
    65,450 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following