Select * into @Variable where 1=1

[Vikas Kumar]

Hi,

I am trying to copy one table's records into a variable named table as below,

Declare @Testing Varchar(20)
Set @Testing = 'Mytable'
Select * into @Testing from samples where 1=1

Here Source Table is 'Samples' and wants to get all its records into '@testing' Table. But it gives an error 'Incorrect syntax near '@testing'

I'm on Sql server 2014. Any help would be appreciated.

Thanks.

 

[Micron]

Not clear what you have/are doing where. This is an Access forum, so is that Access code or sql? Or is it sql server sql?
In vba, the first character of a variable must start with a letter and cannot contain @ or a few other characters. I don't think Varchar has any meaning in Access either.

 

[Vikas Kumar]

Not clear what you have/are doing where. This is an Access forum, so is that Access code or sql? Or is it sql server sql?
In vba, the first character of a variable must start with a letter and cannot contain @ or a few other characters. I don't think Varchar has any meaning in Access either.

I know its a MS Access forum that's why I mentioned that 'I am on sql server 2014'. And in ssms variable declaration requires '@' together with variable name.

Let me try again to make it clearer, Here are the comments for each line of the code,

Declare @Testing Varchar(20) -- Declaring a Variable in memory named = Testing which data type is Varchar
Set @Testing = 'Mytable' --Assigning a String value to the variable and that Value is Mytable
Select * into @Testing from samples where 1=1 --Here source table is Samples and wants to copy all its records into Mytable variable

Overall objective is to create a new table and get it filled from source table. I think there is some issue of variable data type but unable to rectify.

Hope It makes sense. Pls let me know if any point requires more explanation.

Thanks again for your concern.

 

[james_lankford]

Execute Dynamic SQL commands in SQL Server

In this tip we look at different approaches for building dynamic SQL code.

www.mssqltips.com

go to to the section named
Dynamic SQL commands using EXEC Statement

 

[Vikas Kumar]

Execute Dynamic SQL commands in SQL Server

In this tip we look at different approaches for building dynamic SQL code.

www.mssqltips.com

go to to the section named
Dynamic SQL commands using EXEC Statement

It is useful but not exactly what I am looking for. As I want to avoid stored procedure here. See similar query below:

Select * Into Mytable from Samples where 1=1

This one creates a new Table named 'Mytable' and will copy all records of source table (samples) into it. The query works successfully but I want to do a slight modification to the hard coded destination table(Mytable) to pick its value from a variable.

Thanks

 

[Micron]

Yes, I saw that you mentioned that but I know that you can use Access as the front end for a SS db so I wasn't sure. I'm afraid I can't help you with SS syntax if that's what that is. It also resembles VBA code, which I would be able to help with. Sorry.

 

[Vikas Kumar]

Yes, I saw that you mentioned that but I know that you can use Access as the front end for a SS db so I wasn't sure. I'm afraid I can't help you with SS syntax if that's what that is. It also resembles VBA code, which I would be able to help with. Sorry.

No problem! You gave your time to look around & It means a lot.

 

[james_lankford]

It is useful but not exactly what I am looking for. As I want to avoid stored procedure here. See similar query below:


This one creates a new Table named 'Mytable' and will copy all records of source table (samples) into it. The query works successfully but I want to do a slight modification to the hard coded destination table(Mytable) to pick its value from a variable.

Thanks

you didn't read the section I mentioned
go to the link
read that section
here is the code from that section
it is not a stored proc

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city

EXEC (@sqlCommand)

 

[Vikas Kumar]

you didn't read the section I mentioned
go to the link
read that section
here is the code from that section
it is not a stored proc

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city

EXEC (@sqlCommand)

thanks for pointing this out. following the section I wrote this as per my need,

Declare @SqlCmd Varchar(1000)
Declare @ColumnList Varchar(75)
Set @ColumnList = '[Booking Date], [Booking Time]'
Set @SqlCmd = 'Select' + @ColumnList + 'From Samples'
Exec (@Sqlcmd)

which is just equivalent to "Select [Booking Date], [Booking Time] from Samples"
It is helpful and meet the criteria till a stage, still there are some points, I couldn't get over to them:

1) In The attached code we need to supply all the fields manually. Like in my source table(samples) there are 70+ fields/columns so how can I reference all of them to @ColumnList variable. as I tried Samples.* and Samples but neither worked.
2) The code makes the source table dynamic while we want to make the dest table as dynamic. Say It just filter/select records but what is next way to get all these records in a new table.

It would be great if we can overcome these points.