SQL_M SQL_M - 1 year ago 60
SQL Question

Using dynamic sql to automate inserts in different tables

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

Answer Source

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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download