CODEWITHSUNDEEP
sql-servert-sql
joe
joe

Reputation: 1473

Insert records into table in SQL server

Is there a way in SQL SERVER(05 & 08) that I can insert records from an external file into a temp table? I do not have privilege to the database at all. Here is what i tried to do:

CREATE table #temp
(KEY_ID INT)

INSERT INTO #temp
SELECT 90883000

Ran #temp table with result:

KEY_ID
---------
90883000

It kind of works with just one record. How do I do if I have hundred records? Thanks a lot!!!

Upvotes: 1

Views: 1591

Answers (4)

RogerRabbit
RogerRabbit

Reputation: 1

This is how I usually do it if I have the values in a file somewhere. Inserting hundreds of records from flat text file into temp table. My example only has one column, but as long as the flat file is delimited somehow, you can do as many columns as needed. Need to make sure you put the text file in a directory you have access to.

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable

CREATE TABLE #TempTable(Col1 varchar(10))   --Specify temp-table name & columns

    BULK INSERT #TempTable
       FROM 'C:\FileName.txt'           --Specify file path/name
       WITH (
         FIELDTERMINATOR = '\t',
         ROWTERMINATOR = '\n'
       );
    GO

Upvotes: 0

Mazhar Karimi
Mazhar Karimi

Reputation:

This just to show how to add multiple rows in a table. 

CREATE table #temp(
   KEY_ID integer
)

Declare @i as int

set @i = 1

WHILE  (@i <= 10000)
BEGIN

insert into #temp values(@i)

set @i += 1

End

Upvotes: 2

Brad
Brad

Reputation: 15577

For importing files, you can use BULK IMPORT. Use this to get the data into your temp table, then you can run set based operations against it.

A more effective way of inserting a lot of rows.

INSERT INTO #temp
(KeyID)
SELECT TOP 1000 -- PUT YOUR QUANTITY HERE
       IDENTITY(INT,1,1) AS N
FROM Master.dbo.SysColumns sc1,
     Master.dbo.SysColumns sc2

If you're going to be using this a lot, just ask them to create you a TALLY TABLE.

Upvotes: 0

Skadoosh
Skadoosh

Reputation: 2523

How about a table variable. I believe that the #temp need rights to the tempdb database. I believe that a table variable is used just like any other variable which is session based.

To declare a table variable:

DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

Insert into a table variable:

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
    FROM [Order Details]
    GROUP BY ProductID

Upvotes: 0

Related Questions