July 10, 2017 at 1:36 pm
How to Create temp table at the time of insert?
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + #tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
This will work only if you have temp table created with columns. But my file already have column names so can i just "Insert Into #temp from @filename" ?
If so what is the syntax?
July 10, 2017 at 1:43 pm
komal145 - Monday, July 10, 2017 1:36 PMHow to Create temp table at the time of insert?
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + #tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
This will work only if you have temp table created with columns. But my file already have column names so can i just "Insert Into #temp from @filename" ?If so what is the syntax?
That format of INSERT only works with BULK INSERT. The question is, what is your objective? You are already inserting data. What is the purpose / objective of having a temp table with the same data? If this is a rather large file, I'm doubtful you want to fill up the tempdb database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2017 at 1:46 pm
komal145 - Monday, July 10, 2017 1:36 PMHow to Create temp table at the time of insert?
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + #tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
This will work only if you have temp table created with columns. But my file already have column names so can i just "Insert Into #temp from @filename" ?If so what is the syntax?
You can't create a table using BULK INSERT. You need to create the table before executing the BULK INSERT.
An alternative could be something like this, but I haven't tested it. SELECT * FROM OPENROWSET(...)
Any way, there's no reason to insert values into a table that you have no idea what structure might have.
July 10, 2017 at 1:51 pm
sgmunson - Monday, July 10, 2017 1:43 PMkomal145 - Monday, July 10, 2017 1:36 PMHow to Create temp table at the time of insert?
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + #tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
This will work only if you have temp table created with columns. But my file already have column names so can i just "Insert Into #temp from @filename" ?If so what is the syntax?
That format of INSERT only works with BULK INSERT. The question is, what is your objective? You are already inserting data. What is the purpose / objective of having a temp table with the same data? If this is a rather large file, I'm doubtful you want to fill up the tempdb database.
I am getting data in a file format , i am laoding that into temptable and do insert into new tables i created.
My objective is i have a file with all column names , everytime I do Bulk insert into a temp table , need to create a temp table with columns , instead can i just create temp table at insert ? as we do in tsql?
July 11, 2017 at 7:41 am
I don't think that's how BULK INSERT operates. My guess is that the whole reason it needs the table in advance is so that it knows exactly what to expect, and can therefore just blast the data in without having to worry about anything else. As the whole idea behind it is to do things fast, you kind of have to know what to expect, so it just wouldn't make sense to have to impose the extra overhead of assessing data types on the fly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2017 at 9:43 am
I've done this with a script task in an SSIS package. it is a good size snippet of code, with lots of caveats.... the end result is exactly what everyone is describing, SOMETHING has to create the table before you can bulk insert into it.
I have to assume the file is well formatted,with the correct number of columns in all rows,
but i grab the first row as a string, and split it into a string array by the delimiter of my choice so i know the number of columns; I have additional logic so i decide whether the first column is the column headers or not. the data is then inserted into a DataTable object.
then after the first 100K rows, I build a create table statement based on the shape of the data in that object. I create a table based on that, which i can then use the sqlBulkLoad operation, and repeat until i have no more rows.
so the script task has to RBAR the data, split it, and insert the array into a datatable, before i can use the .Net's BulkInsert operation
Lowell
July 16, 2017 at 2:34 pm
Luis Cazares - Monday, July 10, 2017 1:46 PMkomal145 - Monday, July 10, 2017 1:36 PMHow to Create temp table at the time of insert?
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + #tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
This will work only if you have temp table created with columns. But my file already have column names so can i just "Insert Into #temp from @filename" ?If so what is the syntax?
You can't create a table using BULK INSERT. You need to create the table before executing the BULK INSERT.
An alternative could be something like this, but I haven't tested it. SELECT * FROM OPENROWSET(...)
Any way, there's no reason to insert values into a table that you have no idea what structure might have.
I already do this. I bulk insert into a table the complete lines from the file, then split it up into its ultimate destination. Maybe its not optimal having to make a second pass over data, but I don't mind spending the cycles in some cases, plus it allows for some adhoc error checking also beyond just having bcp croak and not import anything.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply