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?

Recommended Answers

All 3 Replies

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...

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).

try this

INSERT INTO cltClients (Field1, Field2, Save1, Checkin1, Sandbox1)
    SELECT Field1, Field2, Save1, Checkin1, Sandbox1
    FROM cltClients WITH (NOLOCK)
    WHERE ClientNumber = @Template_Account;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.