Using dynamic sql to automate inserts in different tables

Question

I am trying to automate multiple inserts into multiple tables.

Basically, I have one temp table, with Customer codes and I added a row number. I also have a source tables, which contains the customer codes and personal info. Now I would like to insert alle the Person ID's from customer 1 into #1, all the PersonID's from customer 2 into #2 and so on.

I used this code, but it generates an error:

DECLARE @Customer_Code INT
DECLARE @Row INT = 1
DECLARE @SQL NVARCHAR (MAX)


WHILE @Row = SELECT MAX(Rij) FROM #M

BEGIN 
    SELECT @Customer_Code = Customer_Code FROM #M WHERE RowNr = @Row

    SET @SQL = 

    'SELECT PersonID 
    INTO #@Customer_Code
    FROM T_Sourcetable 
    WHERE YEAR (Date) = 2016 
      AND Customer_Code = @Customer_Code'
    
    EXECUTE (@SQL)
       
    SET @Row = @Row + 1
    SET @SQL = ''
END

Could anyone please help me here. Thanks in advance.

PS. I'm using MS SQL Server 2008.

Sample data:

M: Customer_Code AND RowNumber

1,1 2,2 3,3 4,4

In reality, obviously Customer code != RowNumber.

Source Table: COLUMNS (Customer_Code PersonID)

1, 8 ,
1, 9 ,
1, 10,
1, 11,
2, 9 ,
2, 12,
2, 13,
2, 14,
3, 8 ,
3, 14,
3, 15,
3, 17,
4, 8 ,
4, 10,
4, 12,
4, 14

Result should be:

Table #1: 
PersonID
8
9
10
11

Table #2
PersonID
9
12
13
14

Table #3
PersonID
8
14
15
17

Table #4
PersonID
8
10
12
14

Please edit your question to include sample data as DDL+DML (Create and insert statements) and desired results. — Zohar Peled
– Zohar Peled, Commented Aug 16, 2017 at 9:23
Looks kind of scrappy, not used to this editor. Hopefully you can figure out what I mean. — SQL_M
– SQL_M, Commented Aug 16, 2017 at 9:31
I don't want to figure out what you mean, I want to copy and paste the sample data and have it ready for work. — Zohar Peled
– Zohar Peled, Commented Aug 16, 2017 at 9:33
Edited again. Two columns in source table. Output that I wish is clear. — SQL_M
– SQL_M, Commented Aug 16, 2017 at 9:40
Mixing temporary tables and dynamic sql is messy. If the temporary table is created in dynamic sql it will only be available in the dynamic sql context, but will not be available in the context that executes the dynamic sql. I think you better describe your final goal so that perhaps someone can help you find a better solution. (Is this an XYPropblem?) btw, I've edited your question again to show you exactly what I mean by sample data. — Zohar Peled
– Zohar Peled, Commented Aug 16, 2017 at 9:51

HoneyBadger · Accepted Answer · 2017-08-16 09:43:04Z

1

You are using WHILE @Row. This is not a boolean expression, you are probably looking for something like WHILE @ROW <= SELECT MAX(Rij) FROM #M.

Furthermore, you are executing the SELECT...INTO multiple times. The second time it gets executed, the table #@Customer_Code already exists, so you can't do SELECT...INTO but have to use INSERT instead. It's best to define the table before hand. edit: I suppose you are trying to make a temporary table for every Customer_Code, in which case you should append the variable correctly. The same goes for AND Customer_Code = @Customer_Code', the variable is appended as the literal string, not as the value it holds. The query that gets executed for every row in #M is:

SELECT PersonID 
    INTO #@Customer_Code
    FROM T_Sourcetable 
    WHERE YEAR (Date) = 2016 
      AND Customer_Code = @Customer_Code

This is obviously not your intention. What you need is:

'SELECT PersonID 
    INTO #'+ @Customer_Code + 
    'FROM T_Sourcetable 
    WHERE YEAR (Date) = 2016 
      AND Customer_Code = '+ @Customer_Code

In future, if you receive an error message, the least you can do is tell us what the error message actually says!

edited Aug 16, 2017 at 9:43

answered Aug 16, 2017 at 9:33

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SQL_M Over a year ago

Ok, you are right: Msg 105, Level 15, State 1, Line 18 Unclosed quotation mark after the character string ' AND Customer_Code= @Customer_Code'.

Alfaiz Ahmed Over a year ago

What Does #m Contain

SQL_M Over a year ago

Thanks @HoneyBadger: This seems to be what I am looking for. Still get an error, Incorrect syntax near T_Sourcetable.

HoneyBadger Over a year ago

Your posted query, sample data and comments don't fit each other. What is #M, now you are talking about T_Sourcetable? You are not making it easy to get the help you need.

SQL_M Over a year ago

People can also edit my input. Makes it a little more challenging for me. Basically, I have one temp table #m, with Customer codes and I added a row number. I also have a source tables, which contains the customer codes and personal info.

|

Collectives™ on Stack Overflow

Using dynamic sql to automate inserts in different tables

M: Customer_Code AND RowNumber

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

M: Customer_Code AND RowNumber

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related