SELECT INTO a table variable in T-SQL
Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it.
Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. http://odetocode.com/Articles/365.aspx
Short example:
declare @userData TABLE(
name varchar(30) NOT NULL,
oldlocation varchar(30) NOT NULL
)
SELECT name, location
INTO @userData
FROM myTable
INNER JOIN otherTable ON ...
WHERE age >开发者_开发百科; 30
The data in the table variable would be later used to insert/update it back into different tables (mostly copy of the same data with minor updates). The goal of this would be to simply make the script a bit more readable and more easily customisable than doing the SELECT INTO
directly into the right tables.
Performance is not an issue, as the rowcount
is fairly small and it's only manually run when needed.
Try something like this:
DECLARE @userData TABLE(
name varchar(30) NOT NULL,
oldlocation varchar(30) NOT NULL
);
INSERT INTO @userData (name, oldlocation)
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;
The purpose of SELECT INTO
is (per the docs, my emphasis)
To create a new table from values in another table
But you already have a target table! So what you want is
The
INSERT
statement adds one or more new rows to a tableYou can specify the data values in the following ways:
...
By using a
SELECT
subquery to specify the data values for one or more rows, such as:INSERT INTO MyTable (PriKey, Description) SELECT ForeignKey, Description FROM SomeView
And in this syntax, it's allowed for MyTable
to be a table variable.
You can also use common table expressions to store temporary datasets. They are more elegant and adhoc friendly:
WITH userData (name, oldlocation)
AS
(
SELECT name, location
FROM myTable INNER JOIN
otherTable ON ...
WHERE age>30
)
SELECT *
FROM userData -- you can also reuse the recordset in subqueries and joins
You could try using temporary tables...if you are not doing it from an application. (It may be ok to run this manually)
SELECT name, location INTO #userData FROM myTable
INNER JOIN otherTable ON ...
WHERE age>30
You skip the effort to declare the table that way... Helps for adhoc queries...This creates a local temp table which wont be visible to other sessions unless you are in the same session. Maybe a problem if you are running query from an app.
if you require it to running on an app, use variables declared this way :
DECLARE @userData TABLE(
name varchar(30) NOT NULL,
oldlocation varchar(30) NOT NULL
);
INSERT INTO @userData
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;
Edit: as many of you mentioned updated visibility to session from connection. Creating temp tables is not an option for web applications, as sessions can be reused, stick to temp variables in those cases
Try to use INSERT
instead of SELECT INTO
:
DECLARE @UserData TABLE(
name varchar(30) NOT NULL,
oldlocation varchar(30) NOT NULL
)
INSERT @UserData
SELECT name, oldlocation
First create a temp table :
Step 1:
create table #tblOm_Temp (
Name varchar(100),
Age Int ,
RollNumber bigint
)
**Step 2: ** Insert Some value in Temp table .
insert into #tblom_temp values('Om Pandey',102,1347)
Step 3: Declare a table Variable to hold temp table data.
declare @tblOm_Variable table(
Name Varchar(100),
Age int,
RollNumber bigint
)
Step 4: select value from temp table and insert into table variable.
insert into @tblOm_Variable select * from #tblom_temp
Finally value is inserted from a temp table to Table variable
Step 5: Can Check inserted value in table variable.
select * from @tblOm_Variable
OK, Now with enough effort i am able to insert into @table using the below :
INSERT @TempWithheldTable SELECT
a.SuspendedReason, a.SuspendedNotes, a.SuspendedBy , a.ReasonCode FROM OPENROWSET( BULK 'C:\DataBases\WithHeld.csv', FORMATFILE = N'C:\DataBases\Format.txt',
ERRORFILE=N'C:\Temp\MovieLensRatings.txt' ) AS a;
The main thing here is selecting columns to insert .
One reason to use SELECT INTO is that it allows you to use IDENTITY:
SELECT IDENTITY(INT,1,1) AS Id, name
INTO #MyTable
FROM (SELECT name FROM AnotherTable) AS t
This would not work with a table variable, which is too bad...
精彩评论