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


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

Leave a Reply