DECLARE and SELECT multiple variables in SQL Server

[JeremyJ]

Hi all,
I'm working on a SQL procedure to insert a row for new clients based on a template account that already exists in the database.
I thought about Declaring a variable and selecting the value from the table one at a time, but with many columns needed, this seems inefficient.

Sample Query:

DECLARE @Template_Account decimal(10,0) = 8;

DECLARE @Field1 ntext = (SELECT Field1 FROM cltClients WITH(NOLOCK) WHERE ClientNumber=@Template_Account)
DECLARE @Field2 ntext = (SELECT Field2 FROM cltClients WITH(NOLOCK) WHERE ClientNumber=@Template_Account)
DECLARE @Save1 bit = (SELECT Save1 FROM cltClients WITH(NOLOCK) WHERE ClientNumber=@Template_Account)
DECLARE @Checkin1 bit = (SELECT Checkin1 FROM cltClients WITH(NOLOCK) WHERE ClientNumber=@Template_Account)
DECLARE @Sandbox1 ntext = (SELECT Sandbox1 FROM cltClients WITH(NOLOCK) WHERE ClientNumber=@Template_Account)

INSERT INTO cltClients (Field1, Field2, Save1, Checkin1, Sandbox1)
VALUES(@Field1,@Field2,@Save1,@Checkin1,@Sandbox1);

I'm looking for a way to basically have one SELECT instead of 5 in the example above. Anyone know if that's possible in SQL Server?

[ryantroop]

You arent far off... however, you leave some interesting questions.. such as why use a decimal for a ClientNumber? Doesn't matter, though..

DECLARE 
 @Template_Account decimal(10,0) = 8,
 @Field1 nvarchar(max),
 @Field2 nvarchar(max),
 @Save1 bit,
 @Checkin1 bit,
 @Sandbox1 nvarchar(max)

 SELECT
      @Field1 = Field1,
      @Field2 = Field2,
      @Save1 = Save1,
      @Checkin1 = Checkin1,
      @Sandbox1 = Sandbox1
  from
    cltClients WITH (NOLOCK)
  WHERE 
      ClientNumber = @Template_Account

INSERT INTO cltClients (Field1, Field2, Save1, Checkin1, Sandbox1)
VALUES(@Field1,@Field2,@Save1,@Checkin1,@Sandbox1);

So what I dont understand... why are we insterting the same data into the same table? I get this is pseudo-code... but :-/ I R Confused...

[JeremyJ]

Thanks for the quick response, that's exactly what I was looking for! :-)

Regarding the decimal type, you're right it doesn't make much sense to use that for an integer, but this is a database for an existing program so I have to match the exact data types.

You're right that this is psuedo-code. In a real upload there will be new data (Client Name, Address, etc.) that populates along with some common settings that we want to pull from a template account (rather than hard-coding that in the query, I thought it would be better to pull the most current settings from the template to make future changes easier).

[kalxas]

try this

INSERT INTO cltClients (Field1, Field2, Save1, Checkin1, Sandbox1)
    SELECT Field1, Field2, Save1, Checkin1, Sandbox1
    FROM cltClients WITH (NOLOCK)
    WHERE ClientNumber = @Template_Account;