Home > IT Answers > Development > Variables in T-SQL
Babar1982
20 pts.
 Variables in T-SQL
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]

Software/Hardware used:
ASKED: July 14, 2009  12:33 PM
UPDATED: July 15, 2009  5:01 PM
RELATED QUESTIONS
Answer Wiki:
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 :)
Last Wiki Answer Submitted:  July 15, 2009  9:43 am  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

I tested your code, and it works for me (SQL Server 2008).

carlosdl
 63,535 pts.
 

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.

Denny Cherry
 64,520 pts.