Is it possible to insert into a table variable without defining the schema?

Is it possible to insert into a table variable without defining the schema?

I need to do this like

Declare @tab1 as table

In开发者_如何学Pythonsert into @tab1 select * from table2

Select * from @tab1

I was trying this query but got error.

Please help.

---

The error is with this line:

Declare @tab1 as table

Incorrect syntax near 'table'.

but you have also made a mistake with your insert which should be:

select * into tab1
from table2

as far as i know you need to declare schema when using table varibles, if you use temp tables you don't so this would work

select * into #tab1
from table2

See msdn

---

The error is in the first line

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'table'.

Instead of Declare @tab1 as table try

Declare @tab1 as table(col1 varchar(100)) i.e. full table schema

e.g.

Declare @tab1 as table(col1 varchar(100))

Insert into @tab1 select [Person Name] from tblInformation

Select * from @tab1

Output:

col1

xyz
abc

---

This will work:

declare @tab table (BillID int)
insert into @tab
select top 10 BillID from tblBill
select * from @tab

This will not work:

select top 10 BillID into @tab from tblBill
select * from @tab

If you want to define the schema of the table var on the fly, you can use something like this:

declare @str varchar(1000)
set @str = 'declare @tab table (BillID int) ' + 
'insert into @tab ' + 'select top 10 BillID from tblBill ' + 'select * from @tab '
exec(@str)

Also, look up (sp_executesql) on Pinal Dave's site for some good advice