MSSQL: Automatically Create table and insert records

 

So we have started to create an IP accounting package the, package will dump to a CSV file on a daily Basis. We needed to be able to report on that, the easiest way is defiantly importing it into SQL server.

So I created a script that will create a table called ‘accounting_datehere’

With our requirements, we needed yesterdays date, as the import would happen once a day to and would happen after the day has ended (12:00am) which would give us the most accurate results. so therefore we needed to change the date stamp on the table name to yesterdays date, by adding a -1 to the Getdate() this would allow us to append yesterdays date.

Once the table is created, it does an insert from a CSV which is using a UNC connection.

 

Here is the code.

   1: Use Master
   2: declare @tablecreate varchar(1000)
   3: set @tablecreate = 'create table [Accounting_'+convert(varchar,getdate()-1,112)+']

   4: (


   5: Date varchar(500),


   6: IP_Address  varchar(500),


   7: Packets varchar(500),


   8: Out_Bytes varchar(500),


   9: In_Bytes varchar(500)




  10: )'


  11: exec (@tablecreate)
  12: go
  13: 
  14: 
  15: declare @insert_command varchar(MAX)
  16: set @insert_command = 'insert into Accounting_'+convert(varchar,getdate()-1,112)+'

  17:


  18: SELECT *




  19: FROM OPENROWSET('


'Microsoft.Jet.OLEDB.4.0'',

  20: '


'Text;database=\\server\share;HDR=NO'',

  21: '


'SELECT * FROM test4.csv'')'
  22: execute (@insert_command)

 

Some things you might need to know, if you need to use a UNC path like I, then your SQL service must be running under an account that does has network access (unlike the default local system account) .

Also that user that is used as the service account must have permissions to the file via the shares.

Thanks needs to go to Adrian Foyn for a little help with this code.

 

Brad

This entry was posted on Friday, June 13th, 2008 at 3:19 am and is filed under Microsoft, SQL Server, Software . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “MSSQL: Automatically Create table and insert records”

Leave a Reply