Dynamic insert into table with datetime and int columns

Hello,
Me again..sorry about that!
Here is an excerpt from my stored procdure:
DECLARE
@.NbRecs int,
@.StartTime datetime,
@.EndTime datetime,
@.TableName varchar(50),
@.JobLog varchar(50),
@.DBName varchar(50)
@.SQL varchar(8000)
SET @.JobLog = @.DBName + '.dbo.DownloadJobLog'
IF OBJECT_ID(@.JobLog) IS NULL
BEGIN
SET @.SQL = 'CREATE TABLE ' + @.JobLog +
'(TableName varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
NbRecords int NOT NULL DEFAULT 0)
ON [PRIMARY]'
EXEC(@.SQL)
END
SET @.TableName = 'DOWNLOAD_START'
SET @.StartTime = CURRENT_TIMESTAMP
SET @.EndTime = CURRENT_TIMESTAMP
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
', ' + @.Nbrecs + ')'
EXEC(@.SQL)
The create table portion works fine.
I get the following error message for the dynamic insert:
"Syntax error converting datetime from character
string."
Please help.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1First of all, don't do this. This is really poor practice. Post more specs
and we can help you find a much better solution.
That aside, there are a few things you can do to make your current hmm...
dynamic query work, yet pretty much all of them defy logic. The main problem
with this setup is that while your data is in correct data type you'll have
to mangle it by casting it as character data to build the query string. Ugh!
If you truly are in love with dynamic SQL consider using the sp_executesql
procedure. And please do some reading on the matter here:
http://www.sommarskog.se/dynamic_sql.html
ML|||Thanks for the reply. I had a feeling this was the type of response I was
going to get.
Being a newcomer, I'm still not quite sure why dynamic SQL seems to be such
a
"FAUX PAS" and why it is not recommended, but unfortunately I am confined to
this solution.
That being said, I can create the table using character data types instead o
f
datetime and int if it will
make things easier.
Also what is the advantage of using sp_executesql as opposed to EXEC(@.sql)?
I'm not opposed to sp_executesql and if you can provide an example I will us
e
it.
Briefly, my procedure reads the tables from a linked server(AS400 library),
copies the table structures, adds an identuty column to the new structure an
d
then copies the data to a database on the local server.
Hope this helps and will glady provide more info if necessary
ML wrote:
>First of all, don't do this. This is really poor practice. Post more specs
>and we can help you find a much better solution.
>That aside, there are a few things you can do to make your current hmm...
>dynamic query work, yet pretty much all of them defy logic. The main proble
m
>with this setup is that while your data is in correct data type you'll have
>to mangle it by casting it as character data to build the query string. Ugh
!
>If you truly are in love with dynamic SQL consider using the sp_executesql
>procedure. And please do some reading on the matter here:
>http://www.sommarskog.se/dynamic_sql.html
>ML
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Think of a procedure as a system of pipelines that bring data from a source
outside a database into the database (and vice versa). Would you mix water
and oil in a regular pipeline? :)
sp_executesql will enable you to keep data in apporopriate data types, since
it supports parameters. There are good examples in Books Online:
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
Have you read Erland's article?
ML|||Cismail via webservertalk.com wrote:
> Thanks for the reply. I had a feeling this was the type of response I was
> going to get.
> Being a newcomer, I'm still not quite sure why dynamic SQL seems to be suc
h a
> "FAUX PAS" and why it is not recommended
Then you haven't understood how a tiered architecture works and why we
use client-server databases at all. Nor have you understood why we use
stored procedures. Erland Sommarskog's article in the posted link is
essential reading on dynamic SQL.
I don't understand why you are "confined to this solution" when in your
example the only dynamic code you want to parameterize is the table
name (the rest can be done without dynamic code). Why wouldn't you know
the name(s) of your table(s) at design time? It seems you are creating
tables "on the fly" which is a great way to build a system that will
totally fail in terms of scalability and manageability.
If you genuinely know nothing about the structure of your data source
until runtime then you are probably using the wrong tool. Take a look
at some 3rd party data integration software product that will manage
the metadata for you.
David Portas
SQL Server MVP
--|||I will definately take both your advice and read the article.
In the meantime, perhaps someone can provide a better solution to my problem
.
...
I need to download a bunch(the exact number is irrelevant and it can change
at any time) of files
from the AS400 onto our server. How would you solve this......
David Portas wrote:
>Then you haven't understood how a tiered architecture works and why we
>use client-server databases at all. Nor have you understood why we use
>stored procedures. Erland Sommarskog's article in the posted link is
>essential reading on dynamic SQL.
>I don't understand why you are "confined to this solution" when in your
>example the only dynamic code you want to parameterize is the table
>name (the rest can be done without dynamic code). Why wouldn't you know
>the name(s) of your table(s) at design time? It seems you are creating
>tables "on the fly" which is a great way to build a system that will
>totally fail in terms of scalability and manageability.
>If you genuinely know nothing about the structure of your data source
>until runtime then you are probably using the wrong tool. Take a look
>at some 3rd party data integration software product that will manage
>the metadata for you.
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Cismail via webservertalk.com wrote:
> I need to download a bunch(the exact number is irrelevant and it can chang
e
> at any time) of files
> from the AS400 onto our server. How would you solve this......
>
That would depend on just what the interface is and what the
requirements are. Actually "downloading a bunch of files" isn't really
a SQL Server task. I'll assume that what you really want is to
integrate them into some SQL Server database. You say that the number
of files can change but I'll assume that these files are all of the
same or similar structure - or at least of a structure that you can
determine in advance.
As far as the interface goes, the main options are to download data as
files or to make it available online via an ODBC or OLEDB interface -
as a linked server for example.
One option for actually loading the data is to use a "staging" table.
By that I mean you have a SQL table that approximates the source data
structure. The data is appended to the staging table (maybe with a file
name and line number as additional columns) and then manipulated using
SQL statements to integrate it into your target relational database
schema. This is sometimes called the "ELT" approach: Extract - Load -
Transform.
Another method is "ETL" (Extract - Transform - Load). This usually
involves implementing the Transformation phase in some software tool
outside the database. There are a variety of data integration tools
that will accomplish this for you. If you are downloading raw EBCDIC
files from an AS400 system you will probably need to use such software
to convert them. ETL software tools are particularly useful if the
source structure changes often because the software can maintain its
own database of metadata and validation rules and you may be able to
monitor and change these with a minimum of coding and maintenance.
David Portas
SQL Server MVP
--|||Cismail via webservertalk.com (u14416@.uwe) writes:
> SET @.TableName = 'DOWNLOAD_START'
> SET @.StartTime = CURRENT_TIMESTAMP
> SET @.EndTime = CURRENT_TIMESTAMP
> SET @.SQL = 'INSERT INTO ' + @.JobLog +
> 'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
> ', ' + @.Nbrecs + ')'
> EXEC(@.SQL)
> The create table portion works fine.
> I get the following error message for the dynamic insert:
> "Syntax error converting datetime from character
> string."
You need to convert the datetime values to string to interpolate them
in the string. This is certainly messy. You meed
SELECT @.StartTimeStr = convert(varchar(23), @.Starttime, 126)
And then you must remember to quote them. Using sp_executesql is a lot
easier:
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(@.TableName, @.StartTime, @.EndTime, @.Nbrecs )'
SELET @.params = N'@.TableName varchar(50), @.StartTime datetime, ' +
N'@.EndTime datetime, @.NbRecords int'
EXEC sp_executesql @.SQL, @.params, @.TableName, @.Starttime, @.EndTime,
@.NbRecords
As I can read out from the script, the only thing that's really dynamic
here is the database name?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx